SQL VOLATILE TABLE not working in QlikSense

[Touyets]

Hi all,

In SQL assistant, I am able to run the below SQL script but when running it in QlikSense, it returns a "There were no fields to load in the given source." even though when I run it in SQL assistant, it returns 77k rows.

CREATE MULTISET VOLATILE TABLE MY_DATABASE.MY_VOLATILETABLE AS
(SELECT 
DISTINCT MY_ID
FROM DATABASE.TABLE unique_ids
WHERE REC_TYPE_NM like '%Renew%'
AND IS_DEL_FL = '0'
AND CLSE_DTTM >= CAST('31/03/2018 00:00:00.000000' AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:ss.s(6)')
AND ETL_END_DTTM = CAST('31/12/9999 00:00:00.000000' AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:ss.s(6)')
AND MY_ID IS NOT NULL
AND (AMOUNT_USD_NUMBER >='5000' or AMOUNT_USD_NUMBER ='0'))
WITH DATA
ON COMMIT PRESERVE ROWS;

It must be because when created, SQL will say there are 0 rows processed by default (there are definitely 77k processed).

Here is the SQL assistant output when I create the table and then query it for what's inside using SELECT *:

Statement 1: CREATE TABLE completed. 0 rows processed. Elapsed Time = 00:00:08
Statement 2: SELECT completed. 77026 rows returned. Elapsed Time = 00:00:28
Statement 3: DROP TABLE completed. 0 rows processed. Elapsed Time = 00:00:01

I need to create that volatile table each time I refresh the data in QlikSense so I can't just create teh table in SQL Assistant and then run it in QlikSense separately. How can I bypass that error message in QlikSense?

[Touyets · Accepted solution]

When you are THAT big of an idiot that 5 minutes later you figure it out. smh

QlikSense was returning an error because I was asking it to LOAD all into a QS table immediately after creating the volatile table.  See below:

[Temp Table 1]:
LOAD *;
SQL
CREATE MULTISET VOLATILE TABLE MY_DATABASE.MY_VOLATILETABLE AS
(SELECT 
DISTINCT MY_ID
FROM DATABASE.TABLE unique_ids
WHERE REC_TYPE_NM like '%Renew%'
AND IS_DEL_FL = '0'
AND CLSE_DTTM >= CAST('31/03/2018 00:00:00.000000' AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:ss.s(6)')
AND ETL_END_DTTM = CAST('31/12/9999 00:00:00.000000' AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:ss.s(6)')
AND MY_ID IS NOT NULL
AND (AMOUNT_USD_NUMBER >='5000' or AMOUNT_USD_NUMBER ='0'))
WITH DATA
ON COMMIT PRESERVE ROWS;

It didn't have the time to assess whether the volatile table was actual empty or not and used the # of rows returned by the SQL procedure (0 by default). I just had to remove the LOAD *; line... Working like a charm now.

Whoops.

[winoth]

How are you using the volatile table in Qlik Sense as each SQL statement is executed in different sessions.