4 Replies Latest reply: Dec 21, 2012 6:53 AM by Ian Tsang RSS

    Nested Select using ODBC on Load

    Ian Tsang

      Hi there

       

      I have a question regarding loading data from ODBC using a Nested Select. The SQL was initially developed in MS Access, and tested in SQL Server. The queries both ran fine and bring back the data I need. However, when I try the same query in QlikView, I get an error.

       

      Example data tables and SQL:

      CASE:

      CASE_IDCATEGORYREGIONCASE_DATE
      1AUK01-Apr-12
      2BUK20-Apr-12
      3BUK30-Apr-12
      4BUK11-May-12
      5AUK15-May-12
      6AGB18-Jun-12
      7AUK21-Jun-12
      8BUK25-Jul-12
      9BGB31-Aug-12
      10AUK24-Sep-12

       

      EXTM:

      EXTM_IDCASE_IDBU
      111
      221
      312
      432
      581
      621
      711
      811
      922
      1012

       

      SQL:

      SELECT CASE.CASE_ID, MB.MB
      FROM [CASE] LEFT JOIN [SELECT DISTINCT CASE.CASE_ID, 1 as MB
      FROM CASE LEFT JOIN EXTM ON CASE.CASE_ID = EXTM.CASE_ID
      WHERE EXTM.BU='1']. AS MB ON CASE.CASE_ID = MB.CASE_ID
      WHERE (((CASE.CATEGORY)="B") AND ((CASE.REGION)="UK") AND ((CASE.CASE_DATE) Between #4/1/2010# And #3/31/2014#));
      

       

      OUTPUT:

      CASE_IDMB
      21
      3
      4
      81

       

      What I am doing here is determining whether or not a Case has a related MB, if it does return a 1, if not leave blank.

       

      However, when I tried to use the SQL in a QlikView load, I got this error:

      SQL##f - SqlState: 37000, ErrorCode: 103, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with 'SELECT DISTINCT... is too long. Maximum length is 128.'

       

      Have been stuck for 24 hours now!! Hopefully someone has come across this before and knows a solution?!?

       

      Thanks

       

      QlikNerd

        • Re: Nested Select using ODBC on Load
          Gysbert Wassenaar

          SELECT CASE.CASE_ID, MB.MB

          FROM [CASE] LEFT JOIN [SELECT DISTINCT CASE.CASE_ID, 1 as MB

          FROM CASE LEFT JOIN EXTM ON CASE.CASE_ID = EXTM.CASE_ID

          WHERE EXTM.BU='1']. AS MB ON CASE.CASE_ID = MB.CASE_ID

          WHERE (((CASE.CATEGORY)="B") AND ((CASE.REGION)="UK") AND ((CASE.CASE_DATE) Between #4/1/2010# And #3/31/2014#));

           

          Your subselect is between square brackets. Shouldn't those be parentheses? There's also a dot after the closing square bracket. Possible the square brackets and/or the dot are making the database think that the text between the brackets is a table or field name and that it finds that that name is too long to be valid.

            • Re: Nested Select using ODBC on Load
              Ian Tsang

              Thanks for the reply Gysbert, you're correct in that the QlikView is interpretting the bracketed text as a field name. The SQL is how MS Access outputs and includes the square brackets and the dot. If these are replaced with normal brackets and the dot is removed, then the SQL executes in SQL Server but still won't run in QlikView

               

              I am stumped, and may need to rework the SQL. Although I am SQL/ODBC driven, loading data in QlikView is confusing to me!!

                • Re: Nested Select using ODBC on Load
                  Gysbert Wassenaar

                  It's not Qlikview that interpretting, but the odbc driver. All Qlikview does is hand over the sql statement to the driver. You may indeed want to try to rewrite it. What Access made doesn't make much sense to me. I have no idea if this works, but to me it looks more like what I expect such a statement to look like:

                   

                  SELECT DISTINCT CASE.CASE_ID, 1 as MB

                  FROM [CASE] LEFT JOIN CASE AS MB ON CASE.CASE_ID = MB.CASE_ID

                  LEFT JOIN EXTM ON (MB.CASE_ID = EXTM.CASE_ID and EXTM.BU='1')

                  WHERE (((CASE.CATEGORY)="B") AND ((CASE.REGION)="UK") AND ((CASE.CASE_DATE) Between #4/1/2010# And #3/31/2014#));