This CASE statement is not producing right output

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

General SQL Server Forums

New to SQL Server Programming

This CASE statement is not producing right output

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-02-15 : 05:09:03

Hi all,

I am trying to write a CASE statement where, If defect status flag is N then show Unactioned.

If Defect status flag is Y then look at Job Status Flag and if the Job Status Flag is U then Uncommited, if C then Commited, and if A then Archived. Else '' for everything.

I wrote the CASE statement below to try and do this but I am going wrong somewhere. Anyone know how to do it?

(CASE WHEN
ddata.defect_status_flag = 'N'
THEN
'Unactioned'
WHEN
ddata.defect_status_flag <> 'N'
THEN
(CASE WHEN
ddata.job_status_flag <> 'Y'
THEN
'Uncommited'
WHEN
ddata.job_status_flag = 'C'
THEN
'Commited'
WHEN
ddata.job_status_flag = 'A'
THEN
'Archived'
ELSE
''
END)
ELSE
''
END) as Status_IF_Statement

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 05:17:51

It seems that you are trying to fix up some string for every value in the defect_status_flag column? what are the possible values for "defect_status_flag" column?

come up with few rows as per table structure and let us know whats your required output?

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-15 : 08:18:46

quote:
Originally posted by Maverick_

If Defect status flag is Y then look at Job Status Flag and if the Job Status Flag is U then Uncommited, if C then Commited, and if A then Archived. Else '' for everything.

I wrote the CASE statement below to try and do this but I am going wrong somewhere. Anyone know how to do it?

(CASE WHEN
ddata.job_status_flag <> 'Y'
THEN
'Uncommited'

Your text says you're looking for U, C, A, in job_status_flag, but your conditions in the CASE are <> Y, = C, = A. The way I read the above snippet of code is that every time job_status_flag is not 'Y' (most or all of the time), you will show 'Uncommitted' when you seem to have wanted to do that for values equal to 'U'.

I tried out some test data on your code:

CREATE TABLE #test
(defect_status_flag NVARCHAR(1)
,job_status_flag NVARCHAR(1)
)

INSERT INTO #test
SELECT 'Y', 'U' UNION
SELECT 'Y', 'C' UNION
SELECT 'Y', 'A' UNION
SELECT 'Y', 'B' UNION
SELECT 'N', 'B'

The results:

Status_IF_Statement
-------------------
Unactioned
Uncommited
Uncommited
Uncommited
Uncommited

If I change:

ddata.job_status_flag <> 'Y'

To:

ddata.job_status_flag = 'U'

Results:

Status_IF_Statement
-------------------
Unactioned
Archived

Commited
Uncommited

Hopefully that is closer to what you wanted.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-15 : 10:19:06

Per your requirement....flag = 'Y' then evaluate. If 'N' then 'unactioned'


(CASE WHEN ddata.defect_status_flag = 'Y' 
			THEN (CASE  WHEN  ddata.job_status_flag = 'U'  THEN 'Uncommited'
						WHEN ddata.job_status_flag = 'C'  THEN 'Commited'
						WHEN ddata.job_status_flag = 'A' THEN 'Archived' 
				   ELSE '' END)
				 
	  When ddata.defect_status_flag = 'N' then 'Unactioned'
	  ELSE '' END)
	  
				  as Status_IF_Statement

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources