October 17, 2013 at 3:02 pm
Dear friends,
need a urgent help - Im trying to use CASE statement within a Aggregate/ Group By Caluse , but get error - "Invalid Column Name: [contractor ID],["below is my code-
--
SELECT [WorkForce_WorkOrder].[Work Order ID_WO]
--,[WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO]
,[contractor ID] = CASE
WHEN [WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO] is null
THEN [WorkForce_WorkOrder].[GHRMS Contingent Staff ID]
WHEN [WorkForce_WorkOrder].[GHRMS Contingent Staff ID] is null
THEN [WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO]
END
,[WorkForce_WorkOrder].[Worker ID]
,[WorkForce_WorkOrder].Supplier
,[WorkForce_WorkOrder].Country_WO
,[WorkForce_WorkOrder].[Has the contractor previously worked at HP as a contractor?_WO]
,[WorkForce_WorkOrder].[Has contractor been an HP employee within the past 12 months?_WO]
,[WorkForce_WorkOrder].[Job Posting ID_JP]
--[GHRMS Contingent Staff ID]
,max([Revision #_WO]) as [Revision #_WO]
into #tmp_Distinct_WO_REV#
FROM SmartLabour.dbo.WorkForce_WorkOrder
GROUP BY [WorkForce_WorkOrder].[Work Order ID_WO], [contractor ID],[WorkForce_WorkOrder].[Worker ID],[WorkForce_WorkOrder].Supplier
,[WorkForce_WorkOrder].Country_WO
,[WorkForce_WorkOrder].[Has the contractor previously worked at HP as a contractor?_WO]
,[WorkForce_WorkOrder].[Has contractor been an HP employee within the past 12 months?_WO]
,[WorkForce_WorkOrder].[Job Posting ID_JP]
--
Thanks
DJ
October 17, 2013 at 3:12 pm
I think you need to repeat the case statement in your group by clause.
SELECT [WorkForce_WorkOrder].[Work Order ID_WO]
--,[WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO]
,[contractor ID] = CASE
WHEN [WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO] is null
THEN [WorkForce_WorkOrder].[GHRMS Contingent Staff ID]
WHEN [WorkForce_WorkOrder].[GHRMS Contingent Staff ID] is null
THEN [WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO]
END
,[WorkForce_WorkOrder].[Worker ID]
,[WorkForce_WorkOrder].Supplier
,[WorkForce_WorkOrder].Country_WO
,[WorkForce_WorkOrder].[Has the contractor previously worked at HP as a contractor?_WO]
,[WorkForce_WorkOrder].[Has contractor been an HP employee within the past 12 months?_WO]
,[WorkForce_WorkOrder].[Job Posting ID_JP]
--[GHRMS Contingent Staff ID]
,max([Revision #_WO]) as [Revision #_WO]
into #tmp_Distinct_WO_REV#
FROM SmartLabour.dbo.WorkForce_WorkOrder
GROUP BY [WorkForce_WorkOrder].[Work Order ID_WO], CASE
WHEN [WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO] is null
THEN [WorkForce_WorkOrder].[GHRMS Contingent Staff ID]
WHEN [WorkForce_WorkOrder].[GHRMS Contingent Staff ID] is null
THEN [WorkForce_WorkOrder].[GHRMS Contingent Worker ID_WO]
END,[WorkForce_WorkOrder].[Worker ID],[WorkForce_WorkOrder].Supplier
,[WorkForce_WorkOrder].Country_WO
,[WorkForce_WorkOrder].[Has the contractor previously worked at HP as a contractor?_WO]
,[WorkForce_WorkOrder].[Has contractor been an HP employee within the past 12 months?_WO]
,[WorkForce_WorkOrder].[Job Posting ID_JP]
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 3:31 pm
Yes you need the case expression in your group by. You also should really consider using aliases in your queries. It would make this a lot more legible. And your column names are downright scary. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2013 at 3:42 pm
Sean Lange (10/17/2013)
Yes you need the case expression in your group by. You also should really consider using aliases in your queries. It would make this a lot more legible. And your column names are downright scary. :w00t:
I agree with you, lol, the column names are too scary !!! :w00t:
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 4:28 pm
Thanks for the help on this jonysuise .
You are a genius 🙂
it is working as desired now.
October 18, 2013 at 12:30 am
dhananjay.nagarkar (10/17/2013)
Thanks for the help on this jonysuise .You are a genius 🙂
it is working as desired now.
Far far far away from being a genius, thanks 😛
Anytime. 😛
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 18, 2013 at 5:22 am
one more help- I just started as a SQL Server 2008 /SSRS report programmer, wanted help to define next steps in this field to grow and learn, any tips welcome please.
thanks
DJ
October 18, 2013 at 7:43 am
dhananjay.nagarkar (10/18/2013)
one more help- I just started as a SQL Server 2008 /SSRS report programmer, wanted help to define next steps in this field to grow and learn, any tips welcome please.thanks
DJ
Hang around the forums on here. Try answering the questions on your own, and then start posting your answers as you gain more confidence. The only you get better at anything is by doing it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply