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.
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?
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
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.
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
WHERE FleetId = '3TPA'
I'm pretty sure that will solve your issue.