Using CASE to open a search

Question

Using CASE to open a search

tkc

SSCommitted

Points: 1535
More actions
September 18, 2003 at 5:07 pm

#106502

Folks,
My client has a particular stat they want calculated -- sometimes for a single nurse, sometimes for all the nurses. Because the logic is pretty snarly -- and often under threat of review and revision! -- I want to put the logic for it in one place, probably a UDF but maybe a stored procedure.
I want to avoid dynamic SQL, though. So I am trying to use the CASE statement to calculate the number based on a UserID parameter. If it's > 0, then we calculate the state for just this one nurse...but if its zero we roll up the numbers for all the nurses.
What I am trying to do is like so...but I can't quite get the logic right. Suggestions?
/* assume @id = 0, or some
integer greater than 0... */
SELECT ... /* various fields */
FROM PatientInfo
WHERE ... /* various parameters */
AND UserID IN (
CASE @id
WHEN 0 THEN (SELECT DISTINCT UserID FROM Nurses )
ELSE @id
END
)
When @id > 0, this works fine. But when 0, the subquery brings back multiple results (of course), and the query fails.
Can anyone suggest another technique, if this one is doomed? Thanks.
- Tom

Viewing 12 posts - 1 through 11 (of 11 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

Here's one method you could try:

 

SELECT ... /* various fields */

FROM PatientInfo

WHERE ... /* various parameters */

AND UserID IN (

   SELECT DISTINCT UserID FROM Nurses WHERE @id = 0

    UNION ALL

   SELECT @id WHERE @id <> 0

)

Cheers,

- Mark

Cheers,
- Mark

philcart SSC-Forever Points: 47794 More actions · Answer 2

Is the single SELECT statement necessary?

Could you acheive the same result by doing the '@id > 0' in an IF statement?

EG:

If @id > 0

Select for one nurse

Else

Select for all nurses

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

mccork SSC-Insane Points: 22288 More actions · Answer 3

Phill,

tkc has indicated what /* various fields* / and /* various parameters */ look like.

I'm assuming they're quite longwinded.

Cheers,

- Mark

Cheers,
- Mark

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Hi tkc,

what about using logical operators instead of CASE to achieve the result?

SELECT ... /* various fields */

FROM PatientInfo

WHERE ... /* various parameters */

AND (UserID IN (SELECT DISTINCT UserID FROM Nurses) AND @id = 0) OR (UserID = @id AND @id > 0))

I'm using a very similar condition in one of my queries and it works fine... hope this helps.

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 5

Well, sailing in the same boat...

But I guess my case is more complicated....in that I have FromSource and ToDestination and both can vary Specific or ALL so 4 combi!!!

I am looking for a better soln...

How about something like this?

CREATE TABLE #tmpNurse

(

UserID int

)

IF @Id > 0

INSERT INTO #tmpNurse

VALUES (@Id)

ELSE

INSERT INTO #tmpNurse

SELECT DISTINCT UserID FROM Nurses

SELECT ... /* various fields */

FROM PatientInfo

WHERE ... /* various parameters */

AND UserID IN (SELECT UserID FROM #tmpNurse)

DROP TABLE #tmpNurse

Hope this helps u...

Regards,
Sachin Dedhia

tkc SSCommitted Points: 1535 More actions · Answer 6

Thanks, everyone! I do love this community, for just this reason.

Mark and Sachindedhiya -- for the purposes of my actual task at

hand, it seems like both of your suggestions are very applicable

and I'll be using one of them for sure. I do appreciate it.

- Tom

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 7

Hi Tom,

Thanks for the appreciation...

:))

How about trying this???

SELECT ... /* various fields */

FROM PatientInfo

WHERE UserID = CASE

WHEN @Id > 0 THEN @Id

ELSE UserID

END

I guess this eliminates the need to query the Nurse table???

IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!

SD

Regards,
Sachin Dedhia

tkc SSCommitted Points: 1535 More actions · Answer 8

sachindedhiya

quote:
How about trying this???
SELECT ... /* various fields */
FROM PatientInfo
WHERE UserID = CASE
WHEN @Id > 0 THEN @Id
ELSE UserID
END

Excellent! Perfect! Exactly what I needed. I knew there must be a CASE way to do this that was right to the point. I tried this out with some sample code, and it works great. Easy to work into my existing queries, too.

- Tom

David Burrows SSC Guru Points: 65142 More actions · Answer 9

A bit late but if you want a solution without CASE then

AND (@id = 0 or @id = UserID)

Edited by - davidburrows on 09/24/2003 07:12:48 AM

Far away is close at hand in the images of elsewhere.
Anon.

tkc SSCommitted Points: 1535 More actions · Answer 10

quote:
A bit late but if you want a solution without CASE then
AND (@id = 0 or @id = UserID)

Dang. That's so succinct, it's kind of startling.

Best solution yet! Thanks. Kind of dawns on me how complicated I was making this all before...

- Tom

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 11

2 Good...Perfect & Excellent....

Sachin

🙂

Regards,
Sachin Dedhia