Using an inline function and CTE in a SQL query

Question

I'm trying to use both an inline function and a CTE in a SQL query, and I can't seem to figure out how to do it. I can do one or the other, but not both.

For example (boiled down to the bare minimum of useless queries), I can do:

WITH
    FUNCTION f_test RETURN NUMBER IS
    BEGIN
        RETURN 1;
    END;
SELECT
    f_test()
FROM
    dual;

and I can do this:

WITH cte_test AS (
    SELECT
        1
    FROM
        dual
)
SELECT
    *
FROM
    cte_test

How can I have both the function as well as the CTE defined for the select statement? Basically I want to end up with something like:

SELECT f_test(), cte_test.* FROM cte_test

MT0 · Accepted Answer · 2020-01-31 01:52:14Z

7

The syntax is given in the Oracle SELECT documentation which to combine your inline function and subquery factoring clause would be:

WITH
    FUNCTION f_test RETURN NUMBER IS
    BEGIN
        RETURN 1;
    END;
cte_test ( id ) AS (
  SELECT 1 FROM DUAL
)
SELECT f_test(),
       c.*
FROM   cte_test c;

Which outputs:

F_TEST() | ID
-------: | -:
       1 |  1

db<>fiddle here

edited Jan 31, 2020 at 1:52

answered Jan 31, 2020 at 1:47

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Emily Johnson Over a year ago

Thanks! I would also like to note that I had to add a / to the end to get it to run properly (at least in sql developer), which isn't necessarily true of a select statement without the inline function.

Collectives™ on Stack Overflow

Using an inline function and CTE in a SQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related