Working with Aliased Columns in SQL Server 2012 -

okay, know this question why cannot reference aliased column where, group by, or having statements.

my problem have query being moved teradata database sql server 2012.

in teradata, referencing aliased column in where, group by, having, , join statements valid.

my question is, how can perform query, , others it, in sql server, without having resort populating temporary table select first. (this example single part of large tsql script containing 10 separate transactions, many of or more complex example provided)

select      max(case when              field_name = 'parent brand cd' ,              datalength(field_val)>1          substring(field_val, 1, datalength(field_val) - 1)          else null          end         ) parent_brand_cd,     max(case when              field_name = 'brand id' ,              datalength(field_val)>1          substring(field_val, 1, datalength(field_val) - 1)          else null          end         ) hotel_cd,     @src_sys_id src_sys_id,     max(case when              field_name = 'brand' ,              datalength(field_val)>1          substring(field_val, 1, datalength(field_val) - 1)          else null          end         ) temp1,     case temp1          when 'company1'              'c1'         when 'company2'              'c2'          when 'company3'              'c3'          else temp1      end brand_cd,     @process_id insert_process_id  dbo.company -- staging  group parent_brand_cd   having      parent_brand_cd not null ,      hotel_cd not null 

the first issue query is creating aliased column temp1 , attempting perform case statement against it. can correct doing this:

max(case when              field_name = 'brand' ,              datalength(field_val)>1          substring(field_val, 1, datalength(field_val) - 1)          else null          end         ) temp1,     max(case when              field_name = 'brand' ,              datalength(field_val)>1                       case                  when substring(field_val, 1, datalength(field_val) - 1) = 'company1' 'c1'                 when substring(field_val, 1, datalength(field_val) - 1) = 'company2' 'c2'                 when substring(field_val, 1, datalength(field_val) - 1) = 'company' 'c3'                 else substring(field_val, 1, datalength(field_val) - 1)             end         else null          end         ) brand_cd, 

but not on other parts of script aliased columns calculation used in other calculations. also, not solve issue of aliased columns in group by or having statements.

is there way around limitations of aliased columns in sql server, without having create , populate temporary tables on place?

edit: working solution proposed garethd

select               parent_brand_cd,             hotel_cd,             @src_sys_id src_sys_id,             case temp1                  when 'company1' 'c1'                 when 'company2' 'c2'                  when 'company3' 'c3'                  else temp1              end brand_cd,             @process_id insert_process_id,             @process_id update_process_id                         (                    select                       max(case when field_name = 'parent brand cd' fieldvalue end) parent_brand_cd,                     max(case when field_name = 'brand id' fieldvalue end) hotel_cd,                     max(case when field_name = 'brand' fieldvalue end) temp1                                         (                         select                               field_name,                             fieldvalue = case when datalength(field_val) > 1 substring(field_val, 1, datalength(field_val) - 1) end                            dev_stg_tb.dbo.company_attributes_3 -- staging                     ) c              ) sub                        parent_brand_cd not null ,              hotel_cd not null         group              parent_brand_cd,             hotel_cd,             temp1 

you can move query subquery, , reference aliases. sql server smart enough able optimise in same way having clause (in tests have done @ least). consider following 2 queries:

select  name, [count]    (   select  name, [count] = count(*)                sys.columns             group name         ) sub   [count] > 1;  select  name, [count] = count(*)    sys.columns group name having count(*) > 1; 

the execution plan both queries same:

so query end like:

select  parent_brand_cd,         hotel_cd,         @src_sys_id src_sys_id,         temp1         case temp1              when 'company1' 'c1'             when 'company2' 'c2'              when 'company3' 'c3'              else temp1          end brand_cd,         @process_id insert_process_id    (   select  max(case when field_name = 'parent brand cd' , datalength(field_val) > 1 substring(field_val, 1, datalength(field_val) - 1) end) parent_brand_cd,                     max(case when field_name = 'brand id' , datalength(field_val) > 1 substring(field_val, 1, datalength(field_val) - 1) end) hotel_cd,                     max(case when field_name = 'brand' , datalength(field_val) > 1 substring(field_val, 1, datalength(field_val) - 1) end) temp1                dbo.company -- staging             group parent_brand_cd          ) sub   parent_brand_cd not null  ,     hotel_cd not null; 

you reduce repeated expressions further using subquery:

select  parent_brand_cd,         hotel_cd,         @src_sys_id src_sys_id,         temp1         case temp1              when 'company1' 'c1'             when 'company2' 'c2'              when 'company3' 'c3'              else temp1          end brand_cd,         @process_id insert_process_id    (   select  max(case when field_name = 'parent brand cd' fieldvalue end) parent_brand_cd,                     max(case when field_name = 'brand id' fieldvalue end) hotel_cd,                     max(case when field_name = 'brand' fieldvalue end) temp1                (   select  parent_brand_cd,                                 field_name,                                 fieldvalue = case when datalength(field_val) > 1 substring(field_val, 1, datalength(field_val) - 1) end                            dbo.company -- staging                     ) c             group parent_brand_cd          ) sub   parent_brand_cd not null  ,     hotel_cd not null; 

note, have removed else null case expressions redundant.

i big fan of using common table expressions instead of subqueries de-clutter queries (this entirely subjective), , using pivot, rewrite above as:

with companycte (   select  parent_brand_cd,             field_name,             fieldvalue = case when datalength(field_val) > 1                              substring(field_val, 1, datalength(field_val) - 1)                          end        dbo.company  ) select  pvt.parent_brand_cd         parent_brand_cd = pvt.[parent brand cd],         hotel_cd = pvt.[brand id],         temp1 = pvt.[brand]    companycte c         pivot         (   max(fieldvalue)             field_name in ([parent brand cd], [brand id], [brand])         ) pvt   pvt.[parent brand cd] not null ,     pvt.[brand id] not null; 

the other advantage pivot gives direct access aggregated columns.

of course, other option repeat aggregate function:

having max(case when                  field_name = 'parent brand cd' ,                  datalength(field_val)>1              substring(field_val, 1, datalength(field_val) - 1)              else null              end             ) not null ,    max(case when                  field_name = 'parent brand cd' ,                  datalength(field_val)>1              substring(field_val, 1, datalength(field_val) - 1)              else null              end             ) not null; 

---

addendum

having seen working solution appears don't need grouping @ all, think following work you:

select  parent_brand_cd,         hotel_cd,         @src_sys_id src_sys_id,         temp1         case temp1              when 'company1' 'c1'             when 'company2' 'c2'              when 'company3' 'c3'              else temp1          end brand_cd,         @process_id insert_process_id    (   select  max(case when field_name = 'parent brand cd' fieldvalue end) parent_brand_cd,                     max(case when field_name = 'brand id' fieldvalue end) hotel_cd,                     max(case when field_name = 'brand' fieldvalue end) temp1                (   select  field_name,                                 fieldvalue = case when datalength(field_val) > 1 substring(field_val, 1, datalength(field_val) - 1) end                            dbo.company -- staging                     ) c         ) sub   parent_brand_cd not null  ,     hotel_cd not null; 

the reason being since parent_brand_cd definition unique, since derived aggregate no grouping, further grouping, although inconsequential, redundant.

or pivot solution.

with companycte (   select  field_name,             fieldvalue = case when datalength(field_val) > 1                              substring(field_val, 1, datalength(field_val) - 1)                          end        dbo.company  ) select  parent_brand_cd = pvt.[parent brand cd],         hotel_cd = pvt.[brand id],         src_sys_id = @src_sys_id,         temp1 = pvt.[brand],         brand_cd = case pvt.[brand]                          when 'company1' 'c1'                         when 'company2' 'c2'                          when 'company3' 'c3'                          else temp1                      end,         insert_process_id = @process_id     companycte c         pivot         (   max(fieldvalue)             field_name in ([parent brand cd], [brand id], [brand])         ) pvt   pvt.[parent brand cd] not null ,     pvt.[brand id] not null;