topic Re: Is it possible to use a CASE statement in a SQL query in Connectivity & Data Prep

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

lthurman — Wed, 16 Aug 2017 14:30:48 GMT

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

Wed, 16 Aug 2017 20:45:31 GMT

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

lthurman — Wed, 16 Aug 2017 20:55:17 GMT

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?

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

Wed, 16 Aug 2017 21:30:18 GMT

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.

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

petter — Wed, 16 Aug 2017 22:41:53 GMT

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

sasiparupudi1 — Sun, 20 Aug 2017 14:20:18 GMT

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

duffman1968 — Sun, 20 Aug 2017 21:46:39 GMT

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

lthurman — Mon, 21 Aug 2017 16:15:06 GMT

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

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

petter — Tue, 22 Aug 2017 07:52:45 GMT

Please close the thread by marking it answered