In this pgexercises about joining 3 different tables, the answer is given as following:
select mems.firstname || ' ' || mems.surname as member,
facs.name as facility,
case
when mems.memid = 0 then
bks.slots*facs.guestcost
else
bks.slots*facs.membercost
end as cost
from
cd.members mems
inner join cd.bookings bks
on mems.memid = bks.memid
inner join cd.facilities facs
on bks.facid = facs.facid
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15' and (
(mems.memid = 0 and bks.slots*facs.guestcost > 30) or
(mems.memid != 0 and bks.slots*facs.membercost > 30)
)
order by cost desc;
What I don't understand is that why can't I refer to the SELECT
cost
alias at the WHERE
clause? If i run the same query with
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15' and
cost > 30
order by cost desc;
then an error occurs:
ERROR: column "cost" does not exist
It's clear with me from this answer that it's because of the order of evaluation. But why order by cost desc;
is allowed?
Aucun commentaire:
Enregistrer un commentaire