JoshCheek/sql_needs_a_let_clause.md

## sql_needs_a_let_clause.md

      
    Raw
  

              sql_needs_a_let_clause.md
            
          
    SQL Needs a Let Clause

Working on this challenge: https://pgexercises.com/questions/joins/threejoin2.html
This is what I want to write

select firstname || ' ' || surname       as member,
       name                              as facility,
       slots * slot_cost                 as cost
let    memid = 0                         as is_guest,
       is_guest ? guestcost : membercost as slot_cost
from       cd.bookings
inner join cd.members    using (memid)
inner join cd.facilities using (facid)
where date(starttime) = '2012-09-14'
and   cost > 30
order by cost desc
But there's an issue

I made that let keyword up. SQL doesn't seem to be able to refer
to a calculation by name, except in a select statement. Lets move it in there.
Not awesome, but I can deal.
select firstname || ' ' || surname       as member,
       name                              as facility,
       memid = 0                         as is_guest omit,
       is_guest ? guestcost : membercost as slot_cost omit,
       slots * slot_cost                 as cost
from       cd.bookings
inner join cd.members    using (memid)
inner join cd.facilities using (facid)
where date(starttime) = '2012-09-14'
and   cost > 30
order by cost desc
But there's still an issue

Two issues, actually, with is_guest:

There is no such thing as an omit column (a "virtual" column that exists for the calculation,
but not for the output), and that makes sense, we're in a select clause,
this hypothetical omit keyword is a way of saying we lied about wanting to select it.
The slot_cost calculation can't see it anyway, for... uh... reasons?

So we inline it. Not awesome, but I can deal.
select firstname || ' ' || surname                  as member,
       name                                         as facility,
       slots * (memid = 0) ? guestcost : membercost as cost
from       cd.bookings
inner join cd.members    using (memid)
inner join cd.facilities using (facid)
where date(starttime) = '2012-09-14'
and   cost > 30
order by cost desc
But there's still an issue


SQL doesn't have ternaries.

So we make it a case statement. Not awesome, but I can deal.
select firstname || ' ' || surname  as member,
       name                         as facility,
       case when (memid=0)
       then slots * guestcost
       else slots * membercost
       end                          as cost
from       cd.bookings
inner join cd.members    using (memid)
inner join cd.facilities using (facid)
where date(starttime) = '2012-09-14'
and   cost > 30
order by cost desc
But there's still an issue


Our where clause can't see the cost calculation in cost > 30! I assume this is because
the where operates on an individual record, but the select could potentially
be selecting from a group. In my case, though, it isn't, so IDK.

So we could copy/paste the case statement into the where clause. But I can't deal with that
one so easily. In addition to the clutter, it means that logic is now duplicated.
So, how do we give it a name? After a fair amount of looking, it seems you have
to run a second select on the results of the first select.
I moved the order statement out there, too, figuring it'll be easier to optimize.
select * from (
  select firstname || ' ' || surname  as member,
         name                         as facility,
         case when (memid=0)
         then slots * guestcost
         else slots * membercost
         end                          as cost
  from       cd.bookings
  inner join cd.members    using (memid)
  inner join cd.facilities using (facid)
  where date(starttime) = '2012-09-14'
) temptable
where cost > 30
order by cost desc
Lets use this to name the data?

So, going back to the original desire to name the data that isn't intuitive,
lets use this trick to name the data that I was initially trying to name.
select member, facility, cost from (
  select *, slots * slotcost as cost from (
    select *, case when (isguest)
              then guestcost
              else membercost
              end as slotcost
    from (
      select *,
             firstname || ' ' || surname  as member,
             name                         as facility,
             memid = 0                    as isguest
      from       cd.bookings
      inner join cd.members    using (memid)
      inner join cd.facilities using (facid)
      where date(starttime) = '2012-09-14'
    ) temptable3
  ) temptable2
) temptable1
where   cost > 30
order by cost desc
See how much clearer it became when we named our variables? :/
Yeah, I don't love it, I ultimately went with the previous one.
Their solution

For comparison, this is the solution that the site provided:
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;
Conclusion

I think SQL needs a let clause.
Update

A bit further on, they acknowledged the same issue: https://pgexercises.com/questions/joins/tjsub.html
This is what I wound up with for that one:
with members as (
       select firstname || ' ' || surname as member,
              memid = 0                   as isguest,
             memid                       as memid
       from cd.members
     ),
     sept_14 as (
       select member,
              name as facility,
              ( case when (isguest)
                then slots * guestcost
                else slots * membercost
                end
              ) as cost
       from members
       inner join cd.bookings using (memid)
       inner join cd.facilities using (facid)
       where date(starttime) = '2012-09-14'
     )
select member, facility, cost
from sept_14
where cost > 30
order by cost desc