Using CASE to open a search

Question

Using CASE to open a search

Viewing 11 posts - 1 through 12 (of 12 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: 47797 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: 65144 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