Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You would need to allow non-select queries if you want to use WITH. See:
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
;
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...
No worries 🙂
I'm asking how to make declarations of functions within a WITH clause work in Qlik.