define new variable and use it to define another variable in the same query

Question

In the query below, I would like to create two new variables: one variable derived from postcode, another derived from area (which is derived from postcode).

 select postcode, 
    case when substring(postcode for 2) = 'YO' then 'York',
         when substring(postcode for 1) = 'E' then 'London E'
    ... 
    else 'unknown'
    end as area,
    case when area in ('York', 'London E') then 'England'
         when area = 'Belfast' then 'Northern Ireland'
    else 'unknown'
    end as country
   from countries 
   group by area, country

Initial output:

postcode
SE14 5XG
EX1 3AL
E2 0QP
NW7 2NT
Gu2 8nj

Expected output:

 postcode   area      country
 SE14 5XG   London SE England
 EX1 3AL    Exeter   England
 ...
 NW7 2NT    London NW England
 Gu2 8nj    Guildford England

The code above returns "error: column "area" does not exist"

in PostGreSQL.

ginkul · Accepted Answer · 2021-05-11 10:30:37Z

1

You can't use alias of one column in the other one.

The first thing you could do is to use the same condition for country

...
case when substring(postcode for 2) = 'YO' or substring(postcode for 1) = 'E' 
     then 'England'
     when substring(postcode for 2) ...
else 'unknown'
...

If you really care about the names, you could use a nested query

select postcode, area,
    case when area in ('York', 'London E') then 'England'
         when area = 'Belfast' then 'Northern Ireland'
    else 'unknown'
    end as country
from (
 select postcode, 
    case when substring(postcode for 2) = 'YO' then 'York',
         when substring(postcode for 1) = 'E' then 'London E'
    ... 
    else 'unknown'
    end as area
   from countries 
   group by area
)
group by country;

edited May 11, 2021 at 10:30

answered May 11, 2021 at 10:24

ginkul

1,0761 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-05-11 10:43:44Z

You can use a lateral join to define area:

select c.postcode, v.area,
       (case when v.area in ('York', 'London E') then 'England'
              when v.area = 'Belfast' then 'Northern Ireland'
              else 'unknown'
        end) as country
from countries c cross join lateral
     (values (case when substring(c.postcode for 2) = 'YO' then 'York'
                   when substring(c.postcode for 1) = 'E' then 'London E'
                   ... 
                   else 'unknown'
              end)
     ) v(area);

Note that this fixes some syntax errors and removes the group by (which doesn't make sense in your query).

You might find it simpler to use a lookup:

select c.postcode,
       coalesce(v.area, 'unknown') as area,
       coalesce(v.country, 'unknown') as country
from countries c left join
     (values ('YO%', 'York', 'England'),
             ('E%', 'London E', 'England'),
             . . .
     ) v(pattern, area, country)
     on c.postcode like v.pattern;

Collectives™ on Stack Overflow

define new variable and use it to define another variable in the same query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related