I wrote a query using with statement and union all. But, I am not used to writing complex queries. It keeps breaking in the union all. Please point me where it is wrong?
x
WITH t1 (source, target) AS (
SELECT
t1.study_name,
t1.site_id
FROM iv_p_mv
UNION ALL
SELECT
t2.study_name,
t2.site_id
FROM iv_p_mv as t2 left outer join t1 on t1.study_name = t2.study_name
)
SELECT
study_name,
site_id
FROM t1
Error:
Reference t1 is not found.
Advertisement
Answer
Just out of curiosity. Is this what you need?
WITH source AS (
SELECT t1.study_name, t1.site_id
FROM iv_p_mv t1),
target as (
SELECT t2.study_name, t2.site_id
FROM iv_p_mv t2 LEFT OUTER JOIN source s ON s.study_name = t2.study_name
union all
SELECT s.study_name, s.site_id
FROM source s)
SELECT
study_name,
site_id
FROM target;
@KSp *I can see that you accepted this aswer, but if you want a recursive query, it should look more like (see answer and comments from @mathguy)
WITH t1 (source, target) AS (
SELECT study_name source,
site_id target
FROM iv_p_mv
UNION ALL
SELECT t2.study_name,
t2.site_id
FROM iv_p_mv t2, t1
WHERE t1.source = t2.study_name (+)
)
SELECT source, target FROM t1