ms sql case statement

5 pts.
Tags:
CASE statement
SQL
I have written a case statement in MS SQL that is returning a single phone number for a single id. This is valid as the database contains multiple phone numbers attached to the same id number, with differentiation based on phone type. Once I have found the prefered phone number, I do not want to process any additional records for that id number. If I do not find the prefered phone number, I want to continue until I find a phone number that meets one of the other case statements. If none of the case statements is found, I want it to return a null value.
ASKED: March 6, 2008  7:08 PM
UPDATED: March 6, 2008  8:15 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

HI,

If you have a query now

<pre>
SELECT id
, CASE … END AS preferred_phone
, CASE … END AS bad_phone
, CASE … END AS ugly_phone
FROM …
</pre>

and you want to want to display only one of the phone columns (preferred_phone, if it’s not NULL, otherwise bad_phone, if it’s not NULL, otherwise ugly_phone), then you can say:

<pre>
SELECT id
, COALESCE
( CASE … END — preferred_phone
, CASE … END — bad_phone
, CASE … END — ugly_phone
) AS phone
FROM …
</pre>

Though there may be a more efficient way to get the same results in a single CASE statement,

If that doesn’t solve your problem, could you explain more? Please post some sample data and the results you’d like to get from that data.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following