We help IT Professionals succeed at work.

# Conditional CASE statements

on
206 Views
I have to write a query to select a addressid based on the different criteria for each row. If the the addressid is at the Purchaseorder then use that. If it is not at the PO then use the one which is at the Quote. But remember one PO can be of multiple Quotes so the addressid of max Quote should go there.  If there is no addressid at PO or Quote then use Customer's default addressid.

Can any one tell me how to implement this in SQL?

Thanks,
-BK
Comment
Watch Question

## View Solution Only

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
You can join them and then do a COALESCE.
``````SELECT COALESCE(p.addressid, q.addressid, c.addressid)
FROM PurchaseOrder p
LEFT JOIN (
SELECT poid
, row_number() OVER (ORDER BY quoteid DESC) AS ranking
) q ON p.poid = q.poid AND q.ranking = 1
LEFT JOIN Customer c ON p.customerid = c.customerid
``````
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Type-o -- you will need the table for quotes...
``````SELECT COALESCE(p.addressid, q.addressid, c.addressid)
FROM PurchaseOrder p
LEFT JOIN (
SELECT poid
, row_number() OVER (ORDER BY quoteid DESC) AS ranking
FROM Quotes
) q ON p.poid = q.poid AND q.ranking = 1
LEFT JOIN Customer c ON p.customerid = c.customerid
``````

Commented:
I think a CASE statement is the way to go.  For help on case statements: http://www.google.com/search?rlz=1C1GGLS_en-USUS292US303&ie=UTF-8&q=sql+server+case

You need to break down the logic into pieces:

WHEN (statement that returns true if there is addressid at Purchaseorder )  THEN (select statement that returns addressid at Purchaseorder )
WHEN (statement that returns true if there is addressid at Quote) THEN (select top 1 addressid at Quote order by  Quote (to get max quote) )
END
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
This is an update to a table. I do not know how to write Customer in the CASE and max QuoteID if PO.AddressiD is NULL.
``````This is my code:

UPDATE IC
SET IC.POID = POLI.POID,
END,
END
FROM #InvoiceConsolidation IC
LEFT OUTER JOIN POInfo PO
ON PO.POID = POLI.POID
ON QH.QuoteID = IC.QuoteID
LEFT OUTER JOIN Customer C
ON C.CustomerID = IC.CustomerID
``````

Commented:
Can someone tell me now how to modify my code to get this logic working?
Database Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Sorry, i didnt see the other question, getting lot of mails from EE :)

``````UPDATE IC
SET IC.POID = POLI.POID,
FROM #InvoiceConsolidation IC
LEFT OUTER JOIN POInfo PO ON PO.POID = POLI.POID
LEFT OUTER JOIN QuoteHeader QH 	ON QH.QuoteID = IC.QuoteID
LEFT OUTER JOIN Customer C  ON C.CustomerID = IC.CustomerID
``````
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.