Select Statement with Case - 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

General SQL Server Forums

New to SQL Server Programming

Select Statement with Case

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-25 : 07:10:51

Hi,

I have the following select statement..

   SELECT DISTINCT Snomed,
		case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 THEN 'EMISATT' 
		     WHEN left(Emiscode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' ELSE Emiscode end 		
		Emiscode,ReadCode,Term
   FROM  dbo.CPRDALLMedical WITH (tablock)

dbo.CPRDALLMedical



snomed          EmisCode    ReadCode     Term
2433213         Yhg6fv        NULL       NULL
6243423         9CF           NULL       CVG
3261000006107   EMISATT_12    NULL       Letter
3261000006107   EMISATT_526   NULL       Letter
3261000006107   EMISATT_76    NULL       Letter
3261000006107   EMISATT_888   NULL       Letter
416118004       PCSDT675      NULL       File
416118004       PCSDT87256    NULL       File
547557454       HVG76         NULL       HVD

I would like to have the output as shown below;



snomed          EmisCode    ReadCode     Term
2433213         Yhg6fv        NULL       NULL
6243423         9CF           NULL       CVG
3261000006107   EMISATT       NULL       Letter
416118004       PCSDT         NULL       File
547557454       HVG76         NULL       HVD

Please could anyone help me with the select statement..

Many thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 08:50:59

When I run your query, I get the results you want. So, it is correct as it stands.

However, you should remove the hint WITH (tablock).

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-11-25 : 09:04:18

Remove distinct and add the below groupby statement at the end :
group by Snomed,
(case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 THEN 'EMISATT'
WHEN left(Emiscode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' ELSE Emiscode end)
,ReadCode,Term

Hema Sunder

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 09:10:53

quote:
Originally posted by sunder.bugatha

Remove distinct and add the below groupby statement at the end :
group by Snomed,
(case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 THEN 'EMISATT'
WHEN left(Emiscode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' ELSE Emiscode end)
,ReadCode,Term

Hema Sunder

that's fundamentally the same thing as distinct.

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-11-25 : 09:54:59

Yep just realized..

Hema Sunder

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-25 : 10:51:18

My result when running the code in # 1


snomed          EmisCode    ReadCode     Term
2433213         Yhg6fv        NULL       NULL
6243423         9CF           NULL       CVG
3261000006107   EMISATT       NULL       Letter
416118004       PCSDT675      NULL       File
416118004       PCSDT87256    NULL       File
547557454       HVG76         NULL       HVD

416118004 pulled ALL 2 records instead of one.

Whats wrong?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 11:18:48

I get different results, e.g.


declare @t table (snomed bigint,  EmisCode varchar(20),   ReadCode int,     Term varchar(20))
insert into @t (snomed,          EmisCode,    ReadCode,     Term) values


(2433213         ,'Yhg6fv        ',NULL       ,'NULL  '),
(6243423         ,'9CF           ',NULL       ,'CVG	  '),
(3261000006107   ,'EMISATT_12    ',NULL       ,'Letter'),
(3261000006107   ,'EMISATT_526   ',NULL       ,'Letter'),
(3261000006107   ,'EMISATT_76    ',NULL       ,'Letter'),
(3261000006107   ,'EMISATT_888   ',NULL       ,'Letter'),
(416118004       ,'PCSDT675      ',NULL       ,'File  '),
(416118004       ,'PCSDT87256    ',NULL       ,'File  '),
(547557454       ,'HVG76         ',NULL       ,'HVD	  ')

SELECT DISTINCT Snomed,
		case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 THEN 'EMISATT' 
		     WHEN left(Emiscode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' ELSE Emiscode end 		
		Emiscode,ReadCode,Term
   FROM  @t

returns:


Snomed	        Emiscode	ReadCode	Term
2433213	        Yhg6fv        	NULL	        NULL  
6243423	        9CF           	NULL	        CVG	  
416118004	PCSDT	        NULL	        File  
547557454	HVG76         	NULL	        HVD	  
3261000006107	EMISATT	        NULL	        Letter

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-26 : 09:15:22

Ok - I know what's wrong (my mistake - sorry guys)I need to change the query as shown below;

Note - for the second CASE its a readcode rather than Emiscode, also the data is changed accordingly.


  
INSERT INTO dbo.CPRDLkupMedicalNew (Snomed,Emiscode,ReadCode,Term)
SELECT DISTINCT Snomed,
		case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 THEN 'EMISATT' ELSE Emiscode
		     WHEN left(Readcode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' Else Readcode end 		
		Emiscode,ReadCode,Term
FROM dbo.CPRDALLMedical



snomed          EmisCode    ReadCode     Term
2433213         Yhg6fv        NULL       NULL
6243423         9CF           NULL       CVG
3261000006107   EMISATT_12    NULL       Letter
3261000006107   EMISATT_526   NULL       Letter
3261000006107   EMISATT_76    NULL       Letter
3261000006107   EMISATT_888   NULL       Letter
416118004       NULL          PCSDT675   File
416118004       NULL          PCSDT87256 File
547557454       HVG76         NULL       HVD

I want the result to be


snomed          EmisCode    ReadCode     Term
2433213         Yhg6fv        NULL       NULL
6243423         9CF           NULL       CVG
3261000006107   EMISATT       NULL       Letter
416118004       NULL          PCSDT      File
547557454       HVG76         NULL       HVD

The above select statement is not working - Incorrect syntax near the keyword 'WHEN' error is propmted at runtime and incorrect syntax near 'Emiscode'.

Please review - Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 09:32:38

YOu can't have two ELSE keywords in one CASE statement

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-26 : 09:41:39

Ok - if I remove both ELSE and have it like


   
INSERT INTO dbo.CPRDLkupMedicalNew (Snomed,Emiscode,ReadCode,Term)
SELECT DISTINCT Snomed,
		case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107 THEN 'EMISATT' 
		     WHEN left(Readcode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' end 		
		Emiscode,ReadCode,Term
FROM dbo.CPRDALLMedical

Will the above work?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 10:47:00

Did you run it? Did it work?

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-26 : 11:04:02

Yes I did run it - it runs OK - However, when I check the data it generated its NOT correct..
It inserts PCSDT in Emiscode..

Result is as shown below;


snomed          EmisCode    ReadCode     Term
2433213         Yhg6fv        NULL       NULL
6243423         9CF           NULL       CVG
3261000006107   EMISATT       NULL       Letter
416118004       PCSDT         PCSDT675   File
416118004       PCSDT         PCSDT87256 File
547557454       HVG76         NULL       HVD

Please review for me. Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 11:11:38

Your case statement says

WHEN left(Readcode,5)= 'PCSDT' AND snomed = 416118004 THEN 'PCSDT' end 		
		Emiscode

so it did you you told it to.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-26 : 11:32:34

But its emiscode, readcode, term (these were to be distinct) and the snomed field Correct?

How should it be represneted then?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 12:01:43

Are you trying to use one case statement to affect two columns? You can't do that. Maybe you want:

[code]
INSERT INTO dbo.CPRDLkupMedicalNew (Snomed,Emiscode,ReadCode,Term)
SELECT DISTINCT Snomed,
case WHEN left(Emiscode,7) = 'EMISATT' AND snomed = 3261000006107
THEN 'EMISATT'
ELSE Emiscode
END as Emiscode,
CASE WHEN left(Readcode,5)= 'PCSDT' AND snomed = 416118004
THEN 'PCSDT'
ELSE Readcode
END AS Readcode,
Term
FROM dbo.CPRDALLMedical

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-11-26 : 12:15:06

Thank you so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources