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

[lthurman]

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

[#2]

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.

[lthurman]

Thanks. I’m new to Qlik and am in the “can’t see the forest for the trees” right now. I caught the error. But maybe I should rephrase my other question:

If using the MS SQL Server driver, should I expect any query that works in 2014 to work in Qlik?

[#4]

I do not know.  I am not familiar with the ODBC driver you mentioned.  I have run into other cases with other drivers where they could not handle large volumes of data or BLOBS or Common Table Expressions or syntax that was very database specific.  These are ODBC issues rather than Qlik (or any other client) issues.

I am new to Qlik, too.

[petter]

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.

[sasiparupudi1]

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

;

[duffman1968 · Accepted solution]

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

[lthurman]

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

[petter]

Please close the thread by marking it answered