Jacque Scott
asked on
Creating a complex WHERE clause
I need to get a phone number from a table. I first want to see if there is a 'Work' number. If not then look for a 'Business' number. If not then look for a 'Direct' number. If not then ' '.
Can I write this using one statement?
Table Phone:
pkPhone
fkContacts
PhoneNo
PhoneType
fkContacts PhoneNO PhoneType
12155 (213)555-1212 Direct
12123 (213)566-3344 Home
12123 (213)324-3213 Work
12199 (310)555-1212 Business
Can I write this using one statement?
Table Phone:
pkPhone
fkContacts
PhoneNo
PhoneType
fkContacts PhoneNO PhoneType
12155 (213)555-1212 Direct
12123 (213)566-3344 Home
12123 (213)324-3213 Work
12199 (310)555-1212 Business
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The top clause is a common-table expression. It is equivalent to having a correlated subquery like ...
SELECT ...
FROM ...
WHERE somevalue IN (some other query)
I just find them easier to understand and maintain. The CROSS APPLY is just used to minimize code reuse otherwise I would have to duplicate the entire CASE statement inside the ROW_NUMBER function.
SELECT ...
FROM ...
WHERE somevalue IN (some other query)
I just find them easier to understand and maintain. The CROSS APPLY is just used to minimize code reuse otherwise I would have to duplicate the entire CASE statement inside the ROW_NUMBER function.
ASKER
What is this type of query called?