Selects with Case statement

Question

Post reply

Selects with Case statement

Stephen crocker

SSCrazy

Points: 2512
More actions
May 7, 2007 at 10:33 am

#123693

Hello I hope someone can help with this. I have the following SQL Statement (This came from an Access Query that i am trying to rewrite in SQL)
SELECT tblEXHIBITORS.[MEI ID], tblEXHIBITORS.[Company Name], IIf([new]=-1,"NEW","") AS NewContract, IIf([new]=Yes,[rep name],"") AS RepName2, tblACCOUNTREP.[Rep Name], tblCONTRACT.[Total Booth Cost], tblCONTRACT.[Booth Cost], tblCONTRACT.[Second Story Cost], tblCONTRACT.[Booth Type], tblCONTRACT.[Square Footage], tblCONTRACT.[Booth Number], tblCONTRACT.Year
FROM (tblEXHIBITORS INNER JOIN tblCONTRACT ON tblEXHIBITORS.[MEI ID] = tblCONTRACT.[MEI ID]) INNER JOIN tblACCOUNTREP ON tblEXHIBITORS.AcctRepID = tblACCOUNTREP.AcctRepID
WHERE (((tblCONTRACT.Year)="2008") AND ((tblCONTRACT.Cancelled)=No))
ORDER BY tblEXHIBITORS.[Company Name];
I am unable to get the IIF( color purple) successfully converted to Case statements
I event went as far as looking at the design view in SQL Server to get the statment rewritting but that on I am having difficulty with
I can do the following
Select [new] =
Case
When new = 1 then 'New'
Else ''
End
I need to fingure out how to get this in the top part of the select statement,
Any help would be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

...,

case [new] when -1 then 'NEW'

else ''

end AS NewContract,

case [new] when 'Yes' then [rep name] else '' end AS RepName2, ....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Humm, this should work. Unless, the new column is saved as -1 on the server. What error are you getting? Could you also post some sample data and the required results you need so that we can post the final solution on the first try?

FP-401493 Right there with Babe Points: 723 More actions · Answer 3

Basic case example ... check the output of this script ...

declare @fp int;

set @fp = -1;

select case @fp

when -1 then 'NEW'

else ''

end

set @fp = @fp+1;

select case @fp

when -1 then 'NEW'

else ''

end

I think you can easily convert the case. Also these is one more script from sql server 2000 online help to clear up some confusion.

USE pubsGOSELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END, CAST(title AS varchar(25)) AS 'Shortened Title', price AS PriceFROM titlesWHERE price IS NOT NULLORDER BY type, priceCOMPUTE AVG(price) BY typeGO I hop i helped

FP

True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

Stephen crocker SSCrazy Points: 2512 More actions · Answer 4

ALZDBA, thank you very much

That worked like a champ. You rock