CTE results in Redshift error "column reference is ambiguious for code

user_15361369 0
Viewed 198

What is causing the error "column reference "col_1" is ambiguous?

WITH    cte1 AS
        (
        SELECT  col_1, col_4
        from table_1
        ),
        cte2 AS
        (
        SELECT  two.col_1, two.col_5, three.col_6
        from table_2 as two
        left join table_3 as three
        on two.col_1 = three.col_1 
        ),
        cte3 AS
        (
        SELECT col_1, col_10
        from table_4
)

SELECT  cte1.col_1, cte1.col_4,
        cte2.col_1, cte2.col_5, cte2.col_6,
        cte3.col_1, cte3.col_10
FROM cte1
left join cte2 on cte1.col_1 = cte2.col_6
left join cte3 on cte1.col_1 = cte3.col_10
;

I suspect cte2 is causing the ambiguous column name issue in the final outer query?

amazon-redshift sql
2 Answers

Please check below query. Though it's using garbage data there is no ambiguous reference in the query.

 WITH    cte1 AS
         (
         SELECT  col_1, col_4
         from (select 1 col_1,2 col_4)d
         ),
         cte2 AS
         (
         SELECT  two.col_1, two.col_5, three.col_6
         from (select 1 col_1, 2 col_5) as two
         left join (select 1 col_1,1 col_6) as three
         on two.col_1 = three.col_1 
         ),
         cte3 AS
         (
         SELECT col_1, col_10
         from (select 1 col_1, 2 col_10) table_4
 )
 
 SELECT  cte1.col_1, cte1.col_4,
         cte2.col_1, cte2.col_5, cte2.col_6,
         cte3.col_1, cte3.col_10
 FROM cte1
 left join cte2 on cte1.col_1 = cte2.col_6
 left join cte3 on cte1.col_1 = cte3.col_10

Output:

col_1 col_4 col_1 col_5 col_6 col_1 col_10
1 2 1 2 1 null null

db<fiddle here

user_86516010

put it here since it doesn't fit in comment section.

as I said cte1 and cte3 are not correct statements :

WITH    cte1 AS
        (
        SELECT  col_1, col_4
        ),

it should be something like this :

WITH    cte1 AS
        (
        SELECT  col_1, col_4
        FROM tableName
        ),
user_13674540
Related