SQL: WITH clause with parameters?
18,821
Solution 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
Solution 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'
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Author by
Admin
Updated on July 26, 2022Comments
-
Admin almost 2 years
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
andbars
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.