Invalid column name?

• krypto69

  SSChampion

  Points: 13591

  More actions

  July 19, 2013 at 6:56 am

  #395167

  I have this query works great - no problem:

  select * from (SELECT

  pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2)

  when '01' then 'January' when '02' then 'Feburary' when '03' then 'March' when '04' then 'April'

  when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September'

  when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [month],

  rtrim(eepNameLast) +

  ', ' + rtrim(eepNameFirst) +

  ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,

  eepNameLast AS [Last Name],

  IsNull(eepNameSuffix,'') AS [Suffix],

  eepNameFirst AS [First Name],

  IsNull(eepNameMiddle,'') AS [Middle Name],

  pehCurAmt AS [Current Amount],

  pehCurHrs AS [Current Hours],

  pehCoID AS [Company ID],

  pehEEID AS [EE ID],

  pehEmpNo AS [Emp No],

  pehLocation AS [Location],

  pehJobCode AS [Job Code],

  pehOrgLvl1 AS [Org Level 1],

  pehOrgLvl2 AS [Org Level 2],

  pehOrgLvl3 AS [Org Level 3],

  pehOrgLvl4 AS [Org Level 4],

  pehPayGroup AS [Pay Group],

  pehProject AS [Project],

  pehShfShiftAmt AS [Shift Amount],

  pehearncode AS [Earn Code]

  FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID

  join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t

  inner join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[month] = ob.[month] and orglevel = [org level 2]

  where pehPerControl > '201301011'

  But if I add this to the end:

  AND pehearncode = '0002'

  I get an Invalid column, how is it invalid? It's referenced and returns in the select??

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 19, 2013 at 7:14 am

  #1634005

  You're renamed the column to [Earn Code] in the inner select.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• krypto69

  SSChampion

  Points: 13591

  More actions

  July 19, 2013 at 7:15 am

  #1634007

  geez..thanks Chris...need to get some coffee..

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 19, 2013 at 7:16 am

  #1634008

  krypto69 (7/19/2013)

  ---

  geez..thanks Chris...need to get some coffee..

  Can I have some too? 🙂

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden