Hi,
i've compiled a short demo; it works fine, but if you add in the 'fetch first row only' the result is dead wrong.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0
CREATE OR REPLACE FUNCTION testMacroFunction(inputnumber integer) return varchar2 sql_macro
AS
BEGIN
RETURN q'{
SELECT
field2, field1
FROM ( SELECT 1 AS field1, 'a' AS field2 FROM dual UNION ALL
SELECT 2, 'b1' FROM dual UNION ALL
SELECT 2, 'b2' FROM dual UNION ALL
SELECT 3, 'c' FROM dual) atable
WHERE field1 = testmacrofunction.inputnumber
fetch first row only
}';
END;
/
--vb outer apply
WITH table1 AS
(
SELECT 1 AS col FROM dual UNION ALL
SELECT 2 FROM dual UNION ALL
SELECT 4 FROM dual
)
SELECT table1.col, h.field2, h.field1
FROM table1
outer apply testmacrofunction(table1.col) h
When the ' fetch first row only' is left out, the result is correct:
with ' fetch first row only' left in, the result is this:
while it should be
1 a 1
2 b1 2
4
kind regards,
Nico