Oracle WITH for functions not working

[mattias-thalen]

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

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;
        
SELECT
    TO_ISO8601(sysdate, 'Europe/Stockholm')
    
FROM
    DUAL
;

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.

[Or]

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...

[mattias-thalen]

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
;

[Or]

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...

[mattias-thalen]

No worries 🙂

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