I'm a big fan of the subquery factoring clause.
WITH t1 as (select 1 as id from dual)
select * from t1;
and oracle 12c now includes a PL/SQL declaration section in the WITH clause
WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1
but I can't seem to get them to work together is it possible?
WITH t1 as (select 1 as id from dual)
WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1;
Please refer to the syntax:
https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
As you see, the syntax is:
WITH [ plsql_declarations ] [ subquery_factoring_clause ]
This means that PL/SQL must go first, then a rest of SQL query, in this way:
WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
t1 as (select 1 as id from dual)
SELECT with_function(id)
FROM t1
WHERE rownum = 1;