CODEWITHSUNDEEP
sqlpostgresqlcasewhere-clausecolumn-alias
cinmoy
cinmoy

Reputation: 31

Why a postgresql case statement's result does not work inside where clause

I am solving a PostgreSQL problems in this site.
My answer is:

select concat(mem.firstname, ' ', mem.surname), fac.name as facility,
case when mem.memid=0 then bks.slots*fac.guestcost
     else bks.slots*membercost
end  as cost
from cd.members mem
inner join cd.bookings bks on mem.memid=bks.memid
inner join cd.facilities fac on bks.facid=fac.facid

where bks.starttime >= '2012-09-14' and bks.starttime < '2012-09-15' and cost > 30
order by cost desc;

But it shows error ERROR: column "cost" does not exist for the cost > 30 inside WHERE clause. If the cost works inside ORDER BY, then why it is not working inside WHERE?

The Problem description and database structure

Upvotes: 0

Views: 298

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You cannot use any alias like this in where clause. Instead of t the alias I have used the condition as in select list:

select concat(mem.firstname, ' ', mem.surname), fac.name as facility,
case when mem.memid=0 then bks.slots*fac.guestcost
     else bks.slots*membercost
end  as cost
from cd.members mem
inner join cd.bookings bks on mem.memid=bks.memid
inner join cd.facilities fac on bks.facid=fac.facid

where bks.starttime >= '2012-09-14' and bks.starttime < '2012-09-15' and (case when mem.memid=0 then bks.slots*fac.guestcost
     else bks.slots*membercost
end  ) > 30
order by cost desc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

This is correct. You cannot use a column alias defined in the SELECT in the WHERE. The simple solution is to use a lateral join to define the column alias:

select concat(mem.firstname, ' ', mem.surname), fac.name as facility,
       v.cost
from cd.members mem join
     cd.bookings bks
     on mem.memid = bks.memid join
     cd.facilities fac
     on bks.facid=fac.facid cross join
     (values (case when mem.memid = 0 then bks.slots * fac.guestcost
                   else bks.slots * membercost
              end)
     ) v(cost)
where bks.starttime >= '2012-09-14' and bks.starttime < '2012-09-15' and
      v.cost > 30
order by v.cost desc;

Upvotes: 1

Related Questions