Select case statement - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Select case statement

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-16 : 14:59:06

I am trying to add the select case statement to my main sqlstatement query: it complains there is Incorrect syntax at Then, Can you please help.


SELECT @SQLSelect = 
	CASE 	
	WHEN 'isdate(TAB_ccsNetSU.ToContractorDate) = 1' THEN 'DATEDIFF(Day, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.ToContractorDate) '
	ELSE 'DATEDIFF(Day, TAB_ccsNetSU.PDReceivedDate, GETDATE()) '
END


SET @SQLStatement = 'SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, ' + @SQLSelect + ', 
                      CONVERT(varchar(10), TAB_ccsNetSU.PDNeedDate, 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID '

Thank you very much for the information.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-16 : 15:18:12

Do not use single quotes in
when '.....' then '.....'
and in
else '.....'

It should be
when ..... then .....
else ........

check BOL for more details of using Case - When

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-16 : 15:35:45

Hello Srinika,
Thank you very much, i followed the same way you have mentioned now, i am getting another syntax error: sayin table tab_ccsnetSU does not match with the table name or alias name.


SELECT @SQLSelect = 
	CASE 	
	WHEN isdate(TAB_ccsNetSU.PDToContractorDate) = 1 THEN DATEDIFF(Day, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToContractorDate)
	ELSE DATEDIFF(Day, TAB_ccsNetSU.PDReceivedDate, GETDATE())
	End

SET @SQLStatement = 'SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, ' + @SQLSelect + ', 
                      CONVERT(varchar(10), TAB_ccsNetSU.PDNeedDate, 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID ' + @SQLJoin

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-16 : 15:47:35

Now thats a different issue:

If TAB_ccsNetSU is a table or view
u can't stop from "End"

Learn the proper syntax for Case-When in BOL

SELECT @SQLSelect =
CASE
WHEN ..... then ....
ELSE ...
End
from TAB_ccsNetSU

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-16 : 16:40:29

Hello Srinika,
I did that what you have told me: Now when i execute the stored procedure: i am getting this undef print statement: it says -142 as age


SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, -142 as Age, 
                      CONVERT(varchar(10), TAB_ccsNetSU.PDNeedDate, 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID WHERE     (TAB_ccsNetActions.ModuleName = 'SU')
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription
The 'USP_GetStatReportSU' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

here is my stored proc:
SELECT @SQLSelect = 
	CASE 	
	WHEN isdate(PDToContractorDate) = 1 THEN DATEDIFF(Day, PDReceivedDate, PDToContractorDate)
	ELSE DATEDIFF(Day, PDReceivedDate, GETDATE())
END 
from TAB_ccsNetSU 

SET @SQLStatement = 'SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, ' + @SQLSelect + ' as Age, 
                      CONVERT(varchar(10), TAB_ccsNetSU.PDNeedDate, 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID ' + @SQLJoin

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-16 : 17:00:22

So u think age cannot be -142

I have no idea what u want to do, so does the SQL server I think

U just assign some values to 2 variables, one in a Select statement
the other in a Set statement. So u think the SQL server knows whatever in ur mind ?

U should use Execute or sp_executeSQL (Read BOL) if u want to c the result

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-16 : 19:21:04

Srinika,
I am sorry i did'nt explain to you properly, the problem is i am getting 2 records as a result and for the records the age is showing as -142.

i have the dates filled in such a way for first record the case becomes true and for second one it has to go to else portion.

Is it possible to use a do while loop for each record it checks the condition.

Thank you.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-16 : 19:33:49

Still not clear about ur need!!

If u r sure about ur SQL logic, tell about the code logic.
(ie. u want to create a selection criteria seperately, pass that to a stored procedure, ....) At this stage I'm not checking ur date related conditions etc.

Put the complete code, some sample data and the result u expect, and how do u want to run it (ie. on query analyzer or from an application like VB or ?).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 01:05:27

It is always better to ask questions by providing sample data and the expected result with the query you tried. Refer this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-17 : 09:59:29

Hello Srinika,
Here is the whole stored procedure which i am calling from asp.net

All i am trying to do with the case is get the age in days: if the tocontractordate is filled that means this is closed and the age needs to be calculated using tocontractordate - receiveddate

if the tocontractordate is not filled or used at all that means that record(submittal is till open) to get the age all i want to do is getdate() - receiveddate

thats the only thing i am trying to acheive it using the stored proc's select query.


CREATE PROCEDURE USP_GetStatReportSU
		(@eStatus			nvarchar(20))

AS

DECLARE @ErrorCode				int
DECLARE @SQLStatement 			nvarchar(2000)
DECLARE @SQLJoin 				nvarchar(1000)
DECLARE @SQLSelect 				nvarchar(250)

SET NOCOUNT ON


SELECT @SQLJoin = 
	CASE 	@eStatus
		WHEN 'All' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'

		WHEN 'Open' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'' and isdate(TAB_ccsNetSU.PDToContractorDate) = 0 and isdate(TAB_ccsNetSU.PDReceivedDate) = 1)
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'	
			
		WHEN 'Closed' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'' and isdate(TAB_ccsNetSU.PDToContractorDate) = 1 and isdate(TAB_ccsNetSU.PDReceivedDate) = 1)
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'	 

		WHEN 'Late' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'' and isdate(TAB_ccsNetSU.PDReceivedDate) = 1 and isdate(TAB_ccsNetSU.PDCompleteddate) = 0 and isdate(TAB_ccsNetSU.pdneeddate) = 1 and TAB_ccsNetSU.pdneeddate < getdate())
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate,  TAB_ccsNetSU.PDNeedDate,
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'

		ELSE 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate, TAB_ccsNetSU.PDNeedDate,
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'
END

Select @SQLSelect = 
	CASE 	
	WHEN isdate(PDToContractorDate) = 1 THEN DATEDIFF(Day, PDReceivedDate, PDToContractorDate)
	ELSE DATEDIFF(Day, PDReceivedDate, GETDATE())
END 
from TAB_ccsNetSU 

SET @SQLStatement = 'SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, ' + @SQLSelect + ' as Age, 
                      CONVERT(varchar(10), TAB_ccsNetSU.PDNeedDate, 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID ' + @SQLJoin

EXEC (@SQLStatement)
--SELECT @ErrorCode = @@Error

SET NOCOUNT OFF
RETURN @ErrorCode
GO

Thank you very much.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-17 : 10:28:39

Oh hooo!

I misunderstood ur requirements earlier, Now I think I got

1. All "Select"s to assign to variables should be of the form
Select @MyVariable1 = '...My Text...'

2. In the above u don't need to have the table name (though I said earlier), unless u need that to be in the final query, after concatenation

3. All single quotes inside the string variable should be changed to 2 single quotes
eg. Select @MyVariable1 = ' Where my field = 'a' '
should be changed to
Select @MyVariable1 = ' Where my field = ''a'' '

3. Instead of Select, u can use Set as well in the similar way as above.

4. Once u create ur each String, u can concatenate all

suggestion : write some simple queries in the begining and extend that gradually. (ie without case, ...)
eg. Field list in one variable, table name in another variable and the where clause in another variable would be, a good start. Then extend that with the join & Case-When in field list selection

Just before executing put a print statement and c whether ur Query is OK. I mean, comment the execute part and run the stored procedure (with appropriate parameter) in Query Analyzer tnd get the returned result, copy and paste it on Query Analyzer and run it and c whether u get the desired result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources