2 Replies Latest reply: Dec 18, 2013 6:10 PM by Frank Kulash RSS

    Selecting column from a subquery

    6966d022-9974-456e-8e09-21428c0ac56c

      I have a query with multiple select statements

      select e.employee

      ,e.employee_add

      ,e.employee_city

      , .....

      ,functionconvert(e.employee_id) as employee_number

      case

      if employee_number like "ad%" then "emply from ad"

      if employee_number like "ab%" then "emply from ab"

      else ' '

      end as employee_desc

       

      Then problem is that it returns "employee_number" is not valid identifier

       

      I tried to use a sub-query as:

      case

      if employee_number like "ad%" then "emply from ad"

      if employee_number like "ab%" then "emply from ab"

      else ' '

      end as employee_desc

      from ( select functionconvert(e.employee_id) from employee e)as employee_number

       

      I can find a way around it.

      functionconvert(e.employee_id) as employee_number works but returns severlas codes as 'pa,ab,ad,mx'

      so my problem is that I need to assign a value to employees desc based on  the codes 'ab' or "ad" from employee_number.

       

      Thanks in advance.

        • 1. Re: Selecting column from a subquery
          Solomon Yakobson

          You need to use in-line view or subquery factoring. For example:

           

          with t as (

                     select  e.employee,

                             e.employee_add,

                             e.employee_city,

                             .....,

                             functionconvert(e.employee_id) as employee_number

                       from  employee e

                    )

          select  e.employee,

                  e.employee_add,

                  e.employee_city,

                  .....,

                  case

                    when employee_number like "ad%" then "emply from ad"

                    when employee_number like "ab%" then "emply from ab"

                  end employee_desc

            from  t

          /

           

          SY.

          • 2. Re: Selecting column from a subquery
            Frank Kulash

            Hi,

             

            If you define a column alias (such as employee_number) in a query or sub-query, then you can use that column alias in the ORDER BY clause.  That's the only place you can use the alias in that same query or sub-query.  If you want to use it anywhere else (as you want to use it in the CASE expression), then define the alias in a sub-query, as Solomon showed.  Then you can use the alias wherever you want to in a super-query.