Belun Belun - 3 months ago 9
SQL Question

How to reference a column from inside WITH clause, grabbed with star, in the outside section?

Can I use star inside the WITH clause and then reference columns on the outside query?

The following query does not work (ORA-00904: "MAINQ"."PARTITION_ID": invalid identifier) :

with mainq as (
select *
from table1 s1
left outer join table2 s2
on s1.id = s2.id
and s1.partition_id = s2.partition_id
)
select partition_id from mainq;


I do not want to specify the columns in the WITH section because there are many of them, so it would be more useful for me to just grab all.

Answer

You can make a little edit to your query, maintaining the same meaning:

with mainq as (
 select * 
  from table1 s1
  left outer join table2 s2
   using(id, partition_id)
)
select partition_id from mainq;

This will tell Orale to do the same join as yours, but giving only one occurrence of the columns in the join conditions.

For example:

insert into table1 values (1, 2);
insert into table2 values (1, 2);

SQL> select *
  2    from table1 s1
  3    left outer join table2 s2
  4     using(id, partition_id);

        ID PARTITION_ID
---------- ------------
         1            2

SQL> select *
  2    from table1 s1
  3    left outer join table2 s2
  4     on s1.id = s2.id
  5     and s1.partition_id = s2.partition_id;

        ID PARTITION_ID         ID PARTITION_ID
---------- ------------ ---------- ------------
         1            2          1            2