Forum Stats

  • 3,780,464 Users
  • 2,254,398 Discussions
  • 7,879,339 Comments

Discussions

using outer apply with a table macro function fails when the macro has 'fetch first row only'

User_2OQ7J
User_2OQ7J Member Posts: 2 Green Ribbon

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

Tagged:

Answers