SQL: WITH clause with parameters?

Question

In Oracle SQL Developer, I use the 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 separate the lines "foo" and "bar" so that I 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 that, and there are nuts, only two of them, so it’s a little difficult to maintain.

I know this may not be possible with the WITH clause, but what would be the best solution to avoid writing this subquery several times? It might be pretty simple, but I'm completely new to SQL ...

Thanks for your help.

Answer 1

This seems to 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 does a lot (and not worth 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' 

Answer 2

Try the following:

 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 the pipelined function as follows:

 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 

Answer 3

You can reuse the WITH clause in the following. But as far as I know, you cannot parameterize it. Perhaps this may 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