SQL: WITH clause with parameters?

Question

In Oracle SQL Developer, I am using a WITH clause, in this (simplified) way:

WITH
foos AS
    SELECT *
    FROM my_table
    WHERE field = 'foo'
bars AS
    SELECT *
    FROM my_table
    WHERE field = 'bar'
SELECT *
FROM foo
INNER JOIN bar
ON foo.id = bar.id

I would like to be able to factor out the 'foo' and 'bar' strings, so that I can have something like:

WITH
subq(my_arg) AS
    SELECT *
    FROM my_table
    WHERE field = my_arg
SELECT *
FROM subq('foo')
INNER JOIN subq('bar')
ON subq('foo').id = subq('foo').id

Because, foos and bars are actually much bigger than this, and there are nut just two of them, so it is getting a bit hard to maintain.

I know this may be not possible with a WITH clause, but what would be the best solution to avoid writing this subquery multiple times? This may be actually quite simple, but I am quite new to SQL...

Thanks for your help.

Tennietenniel

Answer 1

You can reuse a WITH expression in the next one. But as far as I know you cannot parametrize it. So may be this could help:

WITH
foosAndbars AS 
    (SELECT *
     FROM [Very complex query] ).
foos AS (
    SELECT *
    FROM foosAndbars 
    WHERE field = 'foo'),
bars AS (
    SELECT *
    FROM foosAndbars 
    WHERE field = 'bar')
SELECT *
FROM foo
INNER JOIN bar
ON foo.id = bar.id

Phenomenology

Answer 2

It seems this may be what you want:

SELECT *
FROM my_table foo
JOIN my_table bar ON foo.id = bar.id
JOIN my_table baz ON foo.id = baz.id
WHERE foo.field = 'foo'
AND bar.field = 'bar'
AND baz.field = 'baz'

If the WITH clause is doing a lot (and worth not repeating):

WITH cte AS SELECT * FROM mytable <with some complex SQL>
SELECT *
FROM cte foo
JOIN cte bar ON foo.id = bar.id
JOIN cte baz ON foo.id = baz.id
WHERE foo.field = 'foo'
AND bar.field = 'bar'
AND baz.field = 'baz'

Ameliaamelie

Answer 3

Try use with function.

Example:

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
/

WITH_FUNCTION(ID)
-----------------
                1

Catastrophe

Answer 4

Try this:

WITH subq AS (
    SELECT *
    FROM my_table
)
SELECT *
  FROM subq s1
     , subq s2
 WHERE s1.id = s2.id
   AND s1.field = 'foo'
   AND s2.field = 'bar'

Or you can use pipelined function like this:

CREATE TYPE t_tf_tab IS TABLE OF MY_TABLE%ROWTYPE;

CREATE OR REPLACE FUNCTION get_table_vals (
    p_val IN VARCHAR
)
RETURN t_tf_tab 
PIPELINED 
AS
BEGIN
  FOR i IN (SELECT * FROM MY_TABLE WHERE FIELD = p_val)
    PIPE ROW(t_tf_row(i.id, i.field, ...));   
  END LOOP;
  RETURN;
END;

SELECT *
  FROM TABLE(get_table_vals('foo')) s1
     , TABLE(get_table_vals('bar')) s2
 where s1.id = s2.id

Census