topic Oracle WITH for functions not working in Connectivity & Data Prep

Oracle WITH for functions not working

mattias-thalen — Mon, 21 Nov 2022 09:32:48 GMT

I'm trying to execute this SQL from Qlik Sense:

But I keep getting this error:

ERROR [HY000] [Qlik][OracleOCI] (3000) Oracle Caller Interface: ORA-00905: missing keyword

But I can run it with no problems in SQL Developer.

Could it be that the Qlik ODBC Connector doesn't allow defining functions within the WITH clause?
Regular subqueries work fine within WITH.

Re: Oracle WITH for functions not working

Or — Mon, 21 Nov 2022 09:44:04 GMT

You would need to allow non-select queries if you want to use WITH. See:

https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/Connectors_ODBC/How-to-load-without-SELECT.htm

Re: Oracle WITH for functions not working

mattias-thalen — Mon, 21 Nov 2022 10:43:31 GMT

I have, hence "Regular subqueries work fine within WITH." 🙂

Working:

WITH tz_table AS ( SELECT 1 AS tz_id , 'Europe/Stockholm' AS timezone FROM DUAL ) , timetable AS ( SELECT 1 AS tz_id , sysdate AS sys_date FROM dual ) SELECT TO_ISO8601(tt.sys_date, tz.timezone) FROM timetable tt LEFT JOIN tz_table tz ON tz.tz_id = tt.tz_id ;

Not working:

WITH FUNCTION TO_ISO8601(datetime IN DATE, timezone IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR( CAST(datetime AS TIMESTAMP) AT TIME ZONE timezone, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM' ); END; tz_table AS ( SELECT 1 AS tz_id , 'Europe/Stockholm' AS timezone FROM DUAL ) , timetable AS ( SELECT 1 AS tz_id , sysdate AS sys_date FROM dual ) SELECT TO_ISO8601(tt.sys_date, tz.timezone) FROM timetable tt LEFT JOIN tz_table tz ON tz.tz_id = tt.tz_id ;

Re: Oracle WITH for functions not working

Or — Mon, 21 Nov 2022 11:24:07 GMT

Apologies - I missed that last part. Unfortunately, I have no idea as to the answer for what you're actually asking, so I'm no help here...

Re: Oracle WITH for functions not working

mattias-thalen — Mon, 21 Nov 2022 11:14:34 GMT

No worries 🙂

I'm asking how to make declarations of functions within a WITH clause work in Qlik.