Hi There, I'm trying to figure out what I may be doing wrong with a Qlik file that seeks to perform counts in a temp table via SQL. The script is longer than what I have posted below, but I'm able to get this to return results in a different query application.
When I add it into my qlikView document, it doesn't throw an error, but it also doesn't return results.
I've been able to narrow it down to the COUNT() function, but I don't understand exactly WHY this isn't working in qlikView... is it a reserved keyword it's not compiling properly, or something else?
SELECT ROW_NUMBER() OVER(PARTITION BY t.Record_ID ORDER BY t.Order_Placed) AS rowNumb,
FROM #treatments AS t
WHERE t.Section_Field_Name = 'Scheduled Treatment #'
, COUNT(ts.RowNumb) OVER(PARTITION BY d.Record_ID) as No_of_Orders
FROM #Data AS d
LEFT JOIN #treatmentSummary AS ts
ON d.Record_ID = ts.Record_ID
When i replace the above red text with
, 1 as No_of_Orders
the code runs.
I know there are some dependent tables missing in the above queries, but does anyone know why this is happening, and how I might be able to get around it? Thanks in advance for your insight!
Thank you Marcus; I'm not sure that will be possible in this case. The temp table has a lot of dependencies to other elements in the SQL script.
I figured out a workaround for this instance, but I'm still just trying to understand WHY this wouldn't work in Qlik. Count() is a pretty common function; why wouldn't Qlik be able to handle it within the SQL code?
Thanks Dathu, and sorry if I'm being unclear. This is all being handled in SQL code, of which COUNT() is a valid function. I'm not 100% sure what you mean by "executing procedures in Qlik", but the entirety of the SQL code is being executed in Qlik via an OLEDB connection.
The script itself should be fine; it executes in other querying tools like Management Studio; I'm trying to understand why it doesn't execute in Qlik while not throwing any errors.
It hasn't anything to do with qlikview if a sql-statement didn't worked then qlikview don't execute the sql - the order is the following: qlikview connects to your database and gives the sql-statement to the odbc/oledb-driver which communicate between database and qlikview which will only return the result of the query.
In your case it could be that your odbc/oledb isn't appropriate for your database respectively to your kind of sql-statement - many driver provides only old standard-sql and not quite new and/or specialized sql-features.