Select in CASE statement

Question

Post reply

Select in CASE statement

CooLDBA

Hall of Fame

Points: 3301
More actions
June 14, 2010 at 9:12 am

#219966

Is this possible?
OraclePartNo = CASE WHEN Oracle_Part_ID IS NOT NULL THEN Oracle_Part_ID
ELSE
SELECT top 1 Oracle_Part_ID FROM Part_Oracle WHERE LOCATION = 'OTT'
END
FROM Part_Oracle
But I'm getting error:
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'END'.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

It is possible but not very good way to do (you need take your select top 1... into brackets):

select

OraclePartNo =

CASE WHEN Oracle_Part_ID IS NOT NULL THEN Oracle_Part_ID

ELSE

(SELECT top 1 Oracle_Part_ID FROM Part_Oracle WHERE LOCATION = 'OTT')

END

FROM Part_Oracle

You better preselect the default value into variable and use it instead:

declare @Default int

SELECT TOP 1 @Default = Oracle_Part_ID

FROM Part_Oracle WHERE LOCATION = 'OTT'

select OraclePartNo = ISNULL(Oracle_Part_ID, @Default )

FROM Part_Oracle

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

CooLDBA Hall of Fame Points: 3301 More actions · Answer 2

Thanks for your reply, Eugene. Forgot the brackets. =(