CODEWITHSUNDEEP
sqloracle-database
tmsblgh
tmsblgh

Reputation: 527

Use alias from subquery

I would like to use aliases from a subquery.

Fruit table:
name:    is_bio:    from_id:   to_id:
Apple    yes        Spain      Italy

Country table:
id:      packaging_type:    packaging_quantity:
Italy    wood box           12
Spain    paper box          18

And I tried something like this:

with subquery as (SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit
    inner join country a on a.country.id = fruit.from_id
    inner join country b on b.country.id = fruit.to_id
WHERE fruit.name = 'Apple')
SELECT a.packaging_quantity from subquery;

But I got an error:

ORA-00904: "A"."PACKAGING_QUANTITY": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 7 Column: 14

Upvotes: 0

Views: 2099

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

Your CTE has four columns, but only two column aliases, packaging_type and packaging_quantity. The table alias is not the full qualified column name (that is, including the alias or table name), but only the column name itself.

You need to give them distinct names using as, such as:

with subquery as (
      select a.packaging_type as packaging_type_a,
             a.packaging_quantity as packaging_quantity_a, 
             b.packaging_type as packaging_type_b,
             b.packaging_quantity as packaging_quantity_b
      from fruit inner join
           country a
           on a.country.id = fruit.from_id inner join
           country b
           on b.country.id = fruit.to_id
      where fruit.name = 'Apple'
     )
select packaging_quantity_a
from subquery;

Upvotes: 4

Related Questions