Help With CASE Statements!!! - 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)

Help With CASE Statements!!!

Author

Topic

murtzzz
Starting Member

14 Posts

Posted - 2007-07-31 : 04:41:40

Hi..

Im currently converting some code from Access to SQL Server.. and I keep on hitting a problem with my CASE statements..

Here is the access code that Im trying to convert...

((IIf([contact_event.practice_code]="V81998","G9999981",IIf(Len([GP code2])<8,"G9999998",IIf([GP code2] Is Null,"G9999998",[GP code2]))))="G9999998")

Here are the CASE statements I have put in my WHERE criteria... (It is attached at the end of other criterias.. hence the starting AND)

AND
CASE WHEN contact_event.practice_code = 'V81998'
THEN 'G9999981'
END
AND
CASE WHEN len(md_datasets.gp_code2) <8
THEN 'G9999998'
END
AND
CASE WHEN md_datasets.gp_code2 is null
THEN 'G9999998'
ELSE md_datasets.gp_code2
END

Please help me! I keep on getting 'Incorrect syntax near the keyword 'AND'' errors!

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 04:47:25

[code]SELECT *,
CASE
WHEN WHEN contact_event.practice_code = 'V81998' THEN 'G9999981'
WHEN len(md_datasets.gp_code2) < 8 THEN 'G9999998'
WHEN md_datasets.gp_code2 is null THEN 'G9999998'
ELSE md_datasets.gp_code2
END AS Code2
FROM Table1[/code]

E 12°55'05.25"
N 56°04'39.16"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-31 : 04:51:14

(Case when [contact_event.practice_code]= 'V81998' Then 'G9999981'
Else
Case when Len([GP code2])<8 Then 'G9999998'
Else
Case when [GP code2] Is Null Then 'G9999998'
Else
[GP code2]
End
End
End)

--------------------------------------------------
S.Ahamed

murtzzz
Starting Member

14 Posts

Posted - 2007-07-31 : 05:12:21

You see the thing is.. I dont want the results to show in a column.. so I cant do an END AS statement.

None of the above work for me.. is it possible to put CASE statements in the WHERE criteria?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 05:51:55

Of course! But why?

SELECT		*
FROM		contact_event
INNER JOIN	md_datasets ON md_datasets.ID = contact_event.ID
WHERE		CASE
			WHEN contact_event.practice_code = 'V81998' THEN 'G9999981'
			WHEN len(md_datasets.gp_code2) < 8 THEN 'G9999998'
			WHEN md_datasets.gp_code2 is null THEN 'G9999998'
			ELSE md_datasets.gp_code2
		END  = <Some value here>

You can put separate WHERE and ANDs instead.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources