Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Invalid column name using CASE

Author  Topic 

redeyedbass
Starting Member

7 Posts
Posted - 2011-08-18 : 11:08:59

Hi all

Here's a query I have:

SELECT
  JRessOnCall.JCallRess_Client_Id
  ,JRessOnCall.JCallRess_Call_Id
  ,Client.Client_UserName
  ,Client.Client_FirstName
  ,Client.Client_LastName
  ,Calls.Call_DateEntered As DateEntered
  ,Calls.Call_DateCompleted
  ,Call_DatePromise
  ,DATEDIFF(hh, GETDATE(), Calls.Call_DatePromise) As SLAExpiryDate 
  ,'SLAExpiry' = CASE 
  		WHEN SLAExpiryDate >= 24 THEN '24 HRS +'
		WHEN SLAExpiryDate >= 12 AND SLAExpiryDate  <= 24 THEN 'BETWEEN 12-24 HRS'
		WHEN SLAExpiryDate >= 8 AND SLAExpiryDate  <= 12 THEN 'BETWEEN 8-12 HRS'
		WHEN SLAExpiryDate >= 4 AND SLAExpiryDate  <= 8 THEN 'BETWEEN 4-8 HRS'
		WHEN SLAExpiryDate >= 2 AND SLAExpiryDate <= 4 THEN 'BETWEEN 2-4 HRS'
		WHEN SLAExpiryDate >= 1 AND SLAExpiryDate <= 2 THEN 'BETWEEN 1-2 HRS'
	END
FROM

.....


I'm getting an error saying "Invalid column name 'SLAExpiryDate'"

I think it'S because you cannot refer to a column you've just created with an As statement.

But you see what I want, so how would you do it ?

Thanks alot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-08-18 : 11:11:36
DO IT LIKE


SELECT other columns...,SLAExpiry
FROM
(
SELECT
  JRessOnCall.JCallRess_Client_Id
  ,JRessOnCall.JCallRess_Call_Id
  ,Client.Client_UserName
  ,Client.Client_FirstName
  ,Client.Client_LastName
  ,Calls.Call_DateEntered As DateEntered
  ,Calls.Call_DateCompleted
  ,Call_DatePromise
  ,DATEDIFF(hh, GETDATE(), Calls.Call_DatePromise) As SLAExpiryDate 
  ,'SLAExpiry' = CASE 
  		WHEN SLAExpiryDate >= 24 THEN '24 HRS +'
		WHEN SLAExpiryDate >= 12 AND SLAExpiryDate  <= 24 THEN 'BETWEEN 12-24 HRS'
		WHEN SLAExpiryDate >= 8 AND SLAExpiryDate  <= 12 THEN 'BETWEEN 8-12 HRS'
		WHEN SLAExpiryDate >= 4 AND SLAExpiryDate  <= 8 THEN 'BETWEEN 4-8 HRS'
		WHEN SLAExpiryDate >= 2 AND SLAExpiryDate <= 4 THEN 'BETWEEN 2-4 HRS'
		WHEN SLAExpiryDate >= 1 AND SLAExpiryDate <= 2 THEN 'BETWEEN 1-2 HRS'
	END
FROM
...
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

redeyedbass
Starting Member

7 Posts
Posted - 2011-08-18 : 11:22:55
OK. Here's the whole original query, which doesn't have the WHERE part since it's an SQL Report Builder Query


SELECT
  JRessOnCall.JCallRess_Client_Id
  ,JRessOnCall.JCallRess_Call_Id
  ,Client.Client_UserName
  ,Client.Client_FirstName
  ,Client.Client_LastName
  ,Calls.Call_HistoFlag
  ,Calls.Call_SC_ID
  ,Calls.Call_LST_QUEUE
  ,Calls.Call_DateEntered As DateEntered
  ,Calls.Call_DateCompleted
  ,Calls.Call_Duration
  ,Call_DatePromise
  ,Client.Client_RessFlag
  ,Client.Client_FirstName + ' ' + Client.Client_LastName As FullName
  ,l1.Lang_Text2 as QueueNameEn
  ,l2.Lang_Text2 As Division
  ,l3.Lang_Text2 as CategoryNameEn
  ,l4.Lang_Text2 as SLAStatusEn
  ,l5.Lang_Text2 as PriorityEn
  ,DATEDIFF(hh, Calls.Call_DateEntered, Calls.Call_DateCompleted) as CallLength
  ,ivProdType.ivProdType_Desc2 As ProductType
  ,DATEDIFF(hh, GETDATE(), Calls.Call_DatePromise) As SLAExpiryDate 
  ,'SLAExpiry' = CASE 
  		WHEN SLAExpiryDate >= 24 THEN '24 HRS +'
		WHEN SLAExpiryDate >= 12 AND SLAExpiryDate  <= 24 THEN 'BETWEEN 12-24 HRS'
		WHEN SLAExpiryDate >= 8 AND SLAExpiryDate  <= 12 THEN 'BETWEEN 8-12 HRS'
		WHEN SLAExpiryDate >= 4 AND SLAExpiryDate  <= 8 THEN 'BETWEEN 4-8 HRS'
		WHEN SLAExpiryDate >= 2 AND SLAExpiryDate <= 4 THEN 'BETWEEN 2-4 HRS'
		WHEN SLAExpiryDate >= 1 AND SLAExpiryDate <= 2 THEN 'BETWEEN 1-2 HRS'
	END
FROM
  JRessOnCall
   INNER JOIN Calls ON Calls.Call_ID = JRessOnCall.JCallRess_Call_Id
   INNER JOIN ivProdType ON ivProdType.ivProdType_ID = Calls.Call_ivProdType_ID
   INNER JOIN Lang l1 ON l1.Lang_Id = Calls.Call_LST_QUEUE
   INNER JOIN Lang l3 ON l3.Lang_Id = Calls.Call_LST_CALLCAT
   INNER JOIN Lang l5 ON l5.Lang_Id = Calls.Call_LST_CALLPRIO
   INNER JOIN C2_Call_UDF ON C2_Call_UDF.CUD_CALL_ID = Calls.Call_ID
   INNER JOIN Lang l2 ON l2.Lang_Id = C2_Call_UDF.CUD_DIVISION
   INNER JOIN Lang l4 ON l4.Lang_Id = C2_Call_UDF.CUD_CBO_SLA_O_N
   INNER JOIN Client ON Client.Client_ID = JRessOnCall.JCallRess_Client_Id


I tried:


SELECT SLAExpiry
FROM
(
SELECT
  JRessOnCall.JCallRess_Client_Id
  ,JRessOnCall.JCallRess_Call_Id
  ,Client.Client_UserName
  ,Client.Client_FirstName
  ,Client.Client_LastName
  ,Calls.Call_HistoFlag
  ,Calls.Call_SC_ID
  ,Calls.Call_LST_QUEUE
  ,Calls.Call_DateEntered As DateEntered
  ,Calls.Call_DateCompleted
  ,Calls.Call_Duration
  ,Call_DatePromise
  ,Client.Client_RessFlag
  ,Client.Client_FirstName + ' ' + Client.Client_LastName As FullName
  ,l1.Lang_Text2 as QueueNameEn
  ,l2.Lang_Text2 As Division
  ,l3.Lang_Text2 as CategoryNameEn
  ,l4.Lang_Text2 as SLAStatusEn
  ,l5.Lang_Text2 as PriorityEn
  ,DATEDIFF(hh, Calls.Call_DateEntered, Calls.Call_DateCompleted) as CallLength
  ,ivProdType.ivProdType_Desc2 As ProductType
  ,DATEDIFF(hh, GETDATE(), Calls.Call_DatePromise) As SLAExpiryDate 
  ,'SLAExpiry' = CASE 
  		WHEN SLAExpiryDate >= 24 THEN '24 HRS +'
		WHEN SLAExpiryDate >= 12 AND SLAExpiryDate  <= 24 THEN 'BETWEEN 12-24 HRS'
		WHEN SLAExpiryDate >= 8 AND SLAExpiryDate  <= 12 THEN 'BETWEEN 8-12 HRS'
		WHEN SLAExpiryDate >= 4 AND SLAExpiryDate  <= 8 THEN 'BETWEEN 4-8 HRS'
		WHEN SLAExpiryDate >= 2 AND SLAExpiryDate <= 4 THEN 'BETWEEN 2-4 HRS'
		WHEN SLAExpiryDate >= 1 AND SLAExpiryDate <= 2 THEN 'BETWEEN 1-2 HRS'
	END
FROM
  JRessOnCall
   INNER JOIN Calls ON Calls.Call_ID = JRessOnCall.JCallRess_Call_Id
   INNER JOIN ivProdType ON ivProdType.ivProdType_ID = Calls.Call_ivProdType_ID
   INNER JOIN Lang l1 ON l1.Lang_Id = Calls.Call_LST_QUEUE
   INNER JOIN Lang l3 ON l3.Lang_Id = Calls.Call_LST_CALLCAT
   INNER JOIN Lang l5 ON l5.Lang_Id = Calls.Call_LST_CALLPRIO
   INNER JOIN C2_Call_UDF ON C2_Call_UDF.CUD_CALL_ID = Calls.Call_ID
   INNER JOIN Lang l2 ON l2.Lang_Id = C2_Call_UDF.CUD_DIVISION
   INNER JOIN Lang l4 ON l4.Lang_Id = C2_Call_UDF.CUD_CBO_SLA_O_N
   INNER JOIN Client ON Client.Client_ID = JRessOnCall.JCallRess_Client_Id
)t


And get exactly the same error. Do I have to SELECT the same columns in both SELECT statements ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-08-18 : 11:28:06
it should be

SELECT *,
,'SLAExpiry' = CASE 
  		WHEN SLAExpiryDate >= 24 THEN '24 HRS +'
		WHEN SLAExpiryDate >= 12 AND SLAExpiryDate  <= 24 THEN 'BETWEEN 12-24 HRS'
		WHEN SLAExpiryDate >= 8 AND SLAExpiryDate  <= 12 THEN 'BETWEEN 8-12 HRS'
		WHEN SLAExpiryDate >= 4 AND SLAExpiryDate  <= 8 THEN 'BETWEEN 4-8 HRS'
		WHEN SLAExpiryDate >= 2 AND SLAExpiryDate <= 4 THEN 'BETWEEN 2-4 HRS'
		WHEN SLAExpiryDate >= 1 AND SLAExpiryDate <= 2 THEN 'BETWEEN 1-2 HRS'
	END
FROM
(
SELECT
  JRessOnCall.JCallRess_Client_Id
  ,JRessOnCall.JCallRess_Call_Id
  ,Client.Client_UserName
  ,Client.Client_FirstName
  ,Client.Client_LastName
  ,Calls.Call_HistoFlag
  ,Calls.Call_SC_ID
  ,Calls.Call_LST_QUEUE
  ,Calls.Call_DateEntered As DateEntered
  ,Calls.Call_DateCompleted
  ,Calls.Call_Duration
  ,Call_DatePromise
  ,Client.Client_RessFlag
  ,Client.Client_FirstName + ' ' + Client.Client_LastName As FullName
  ,l1.Lang_Text2 as QueueNameEn
  ,l2.Lang_Text2 As Division
  ,l3.Lang_Text2 as CategoryNameEn
  ,l4.Lang_Text2 as SLAStatusEn
  ,l5.Lang_Text2 as PriorityEn
  ,DATEDIFF(hh, Calls.Call_DateEntered, Calls.Call_DateCompleted) as CallLength
  ,ivProdType.ivProdType_Desc2 As ProductType
  ,DATEDIFF(hh, GETDATE(), Calls.Call_DatePromise) As SLAExpiryDate   
FROM
  JRessOnCall
   INNER JOIN Calls ON Calls.Call_ID = JRessOnCall.JCallRess_Call_Id
   INNER JOIN ivProdType ON ivProdType.ivProdType_ID = Calls.Call_ivProdType_ID
   INNER JOIN Lang l1 ON l1.Lang_Id = Calls.Call_LST_QUEUE
   INNER JOIN Lang l3 ON l3.Lang_Id = Calls.Call_LST_CALLCAT
   INNER JOIN Lang l5 ON l5.Lang_Id = Calls.Call_LST_CALLPRIO
   INNER JOIN C2_Call_UDF ON C2_Call_UDF.CUD_CALL_ID = Calls.Call_ID
   INNER JOIN Lang l2 ON l2.Lang_Id = C2_Call_UDF.CUD_DIVISION
   INNER JOIN Lang l4 ON l4.Lang_Id = C2_Call_UDF.CUD_CBO_SLA_O_N
   INNER JOIN Client ON Client.Client_ID = JRessOnCall.JCallRess_Client_Id
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

redeyedbass
Starting Member

7 Posts
Posted - 2011-08-18 : 11:34:08

That works wonders. Thanks alot.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-08-18 : 11:36:38
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site