Does a CASE statement lose alias scope in PostgreSQL?

postgresql, sql, sql-order-by

Solution

What you are trying to do is not possible in Postgresql since it doesn't allow you to use an `ALIAS` within the same query as a field. Different from Mysql where you can do it.

To solve your problem you either create your query as a subquery and then your alias will be a field therefore can be used as:

select useid, lobjectid from (
  select
      case
          when sa.luserid > 0 then sa.luserid
          when sa.lgroupid > 0 then sa.lgroupid
          when sa.lldapid > 0 then sa.lldapid
      end as useid,
      lobjectid
   from security s
  left join users u on s.luserid = u.id
  left join usergroups g on s.lgroupid = g.id
  ) as t
order by
        case
            when 'user selection' = 'all objects by user' then useid
            else lobjectid
        end

Or you can repeat the entiry case block

  select
      case
          when sa.luserid > 0 then sa.luserid
          when sa.lgroupid > 0 then sa.lgroupid
          when sa.lldapid > 0 then sa.lldapid
      end as useid,
      lobjectid
   from security s
  left join users u on s.luserid = u.id
  left join usergroups g on s.lgroupid = g.id
order by
        case
            when 'user selection' = 'all objects by user' then 
                  case
                      when sa.luserid > 0 then sa.luserid
                      when sa.lgroupid > 0 then sa.lgroupid
                      when sa.lldapid > 0 then sa.lldapid
                  end
            else lobjectid
        end

Some engines will let you use the order number of the field on the select scope to the order by like:

select a, b, c from sometable order by 1, 2

Which means that this query will be ordered by the fields `a` and `b`

Problem

First of all, this SQL works: ``` select case when s.luserid > 0 then u.szusername when s.lgroupid > 0 then g.szgroup when s.lldapid > 0 then 'LDAP Group' end as name from security s left join users u on s.luserid = u.id left join usergroups g on s.lgroupid = g.id order by name ``` The above block is proving ordering by alias `name` works, as well as declaring alias `name` works while the term `name` is a reserved word, which is not relevant to the question My issue happens when I make a case statement with an alias in it: Pay attention to the alias `useid` ``` select case when sa.luserid > 0 then sa.luserid when sa.lgroupid > 0 then sa.lgroupid when sa.lldapid > 0 then sa.lldapid end as useid, from security s left join users u on s.luserid = u.id left join usergroups g on s.lgroupid = g.id order by case when 'user selection' = 'all objects by user' then useid else s.lobjectid end ``` The text `user selection` is replaced by a parser with literal text before the SQL is run. Both the alias `useid` and `s.lobjectid` are type `bigint`. An error is thrown at `when 'user selection' = 'all objects by user' then useid`. Am I losing scope of the alias `useid` within the CASE statement? Why does this fail when I try to use the alias `useid` here. By the way, this SQL works as well: ``` select case when s.luserid > 0 then u.szusername when s.lgroupid > 0 then g.szgroup when s.lldapid > 0 then 'LDAP Group' end as name from security s left join users u on s.luserid = u.id left join usergroups g on s.lgroupid = g.id order by case when s.lobjectid > 0 then s.lobjectid else s.luserid end ``` The above block is proving that a `CASE` statement within an `ORDER BY` statement does work. All debates over the logical operations of the above block of SQL is irrelevant to the question, for it is simply junk example SQL.

Original source