CODEWITHSUNDEEP
sqlpostgresql
Luc
Luc

Reputation: 747

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

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.

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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;

Upvotes: 0

ginkul
ginkul

Reputation: 1066

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;

Upvotes: 1

Related Questions