8 Replies Latest reply: Aug 22, 2017 3:52 AM by Petter Skjolden RSS

    Is it possible to use a CASE statement in a SQL query

    Lorenzo Thurman

      I'm trying to run this query in the Data Load Editor of Desktop,

       

      SELECT Sum(CASE WHEN CategoryKey = 3 THEN TotAmt          ELSE 0.00 END),

      Sum(CASE WHEN CategoryKey = 3 THEN TotBilledUnits  ELSE 0    END),

      Sum(CASE WHEN CategoryKey = 5 THEN TotAmt          ELSE 0.00 END),

      Sum(CASE WHEN CategoryKey = 5 THEN TotBilledUnits  ELSE 0    END),

        FROM EDM_ExpCatMdlAgg

      WHERE FleetId = '3TPA'

       

      But, I get this error:

      The following error occurred:

      Connector reply error: Unable to get column information for the fields that are used in the query: ERROR [HY000] [Qlik][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near '='. ERROR [HY000] [Qlik][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared.

      The error occurred here:

      ?

      Data has not been loaded. Please correct the error and try loading again.

       

       

      I can run simpler queries, but this one fails and I'm not sure why. I guess my bigger question is how complex a query does the MS SQL Server driver handle?

      Thanks

       

      Message was edited by: Lorenzo Thurman

        • Re: Is it possible to use a CASE statement in a SQL query
          Alfred Yip

          You have an extra comma between your last Sum() and the From clause.

           

          I do not have a definitive answer about the maximum level of complexity that the SQL Server ODBC driver can handle.

          • Re: Is it possible to use a CASE statement in a SQL query
            Petter Skjolden

            This is not a limitation of Qlik and what you write in the load editor. The entire text between SQL and the following semicolon is sent as is to the ODBC driver and it is up to the ODBC-driver and the back-end SQL Server to just execute the TSQL you have written and return results. The error message indicates that you have a problem with your syntax. Have you tried to run the exact same query in a Microsoft query tool like the Microsoft SQL Server Management Studio and got it to work?

             

            The query seems odd to me as you are using the aggregation function Sum() without having a GROUP BY clause in the SQL. Is that really valid TSQL?

             

            Try writing:

             

            SELECT

              CASE WHEN CategoryKey = 3 THEN TotAmt          ELSE 0.00 END,

              CASE WHEN CategoryKey = 3 THEN TotBilledUnits  ELSE 0    END,

              CASE WHEN CategoryKey = 5 THEN TotAmt          ELSE 0.00 END,

              CASE WHEN CategoryKey = 5 THEN TotBilledUnits  ELSE 0    END

            FROM EDM_ExpCatMdlAgg

              WHERE FleetId = '3TPA'

             

             

            You should expect that most queries that work from other query tools work within the Qlik Load Editor. However there are some limitations:

             

            - It has to be a single SQL statement - not multiple

            - If you have a query that returns multiple columns with the same column name but prefixed with a different table named it will run well with most query tools but Qlik can't figure that out because it strips away the qualifier (the table name) and will get columns which have identical names which it doesn't like.

            • Re: Is it possible to use a CASE statement in a SQL query
              Sasidhar Parupudi

              You are missing group by statement in your query.

              Usually the syntax is

               

              Select

              Dim1,

              Dim2,

              Sum (value)

              From your table

              Where keyfield='something'

              Group by

              Dim1,

              Dim2

              ;

              • Re: Is it possible to use a CASE statement in a SQL query
                Patrick Duffner

                Hey Lorenzo,

                 

                I looked this thread over and Petter has some solid advice on running in SQL Server Query analyzer environment to test your SQLs, I always do that. The Group by is something to consider, it looked like you were just summing 4 buckets.

                 

                I believe you need to supply an alias name with an AS clause for each of you SUM statements.

                Your code with an alias added is below in bold and italics:

                 

                SELECT Sum(CASE WHEN CategoryKey = 3 THEN TotAmt          ELSE 0.00 END) AS 'Cat3 TotAmt',

                Sum(CASE WHEN CategoryKey = 3 THEN TotBilledUnits  ELSE 0    END) AS 'Cat3 TotBilledUnits ,

                Sum(CASE WHEN CategoryKey = 5 THEN TotAmt          ELSE 0.00 END) AS 'Cat5 TotAmt',

                Sum(CASE WHEN CategoryKey = 5 THEN TotBilledUnits  ELSE 0    END)AS 'Cat5 TotBilledUnits

                  FROM EDM_ExpCatMdlAgg

                WHERE FleetId = '3TPA'

                 

                I'm pretty sure that will solve  your issue.

                 

                Regards,

                Patrick

                • Re: Is it possible to use a CASE statement in a SQL query
                  Lorenzo Thurman

                  Thanks all, I think I have handle on it now.