?
Solved

Conditional CASE statements

Posted on 2009-05-07
7
Medium Priority
?
183 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:BrookK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330620
You can join them and then do a COALESCE.
SELECT COALESCE(p.addressid, q.addressid, c.addressid)
FROM PurchaseOrder p
LEFT JOIN (
    SELECT poid
    , addressid
    , 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

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330624
Type-o -- you will need the table for quotes...
SELECT COALESCE(p.addressid, q.addressid, c.addressid)
FROM PurchaseOrder p
LEFT JOIN (
    SELECT poid
    , addressid
    , 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

Open in new window

0
 
LVL 9

Expert Comment

by:ezraa
ID: 24330649
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:

select addressid = CASE
   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) )
  ELSE (select Customer's default addressid)
END
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1500 total points
ID: 24330683
Hello BrookK,

SELECT AddressID = CASE  WHEN EXISTS (Select 1 from PurchaseOrder p where c.CustomerID = p.CustomerID  ) THEN (Select AddressID from PurchaseOrder p where c.CustomerID = p.CustomerID  )
                         WHEN EXISTS (SELECT 1 FROM Quote q where c.CustomerID = q.CustomerID ) THEN (SELECT TOP 1 AddressID FROM Quote where c.CustomerID = q.CustomerID  ORDER BY QuoteID DESC )
                         ELSE  AddressID END
FROM Customer c                        
                         


Regards,

Aneesh
0
 

Author Comment

by:BrookK
ID: 24330699
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,
	IC.BillToAddressID = 
	CASE WHEN PO.BillingAddressID IS NULL
		 THEN QH.BillCustomerAddressID
	END,
	IC.ShipToAddressID = 
	CASE WHEN PO.ShippingAddressID IS NULL
		 THEN QH.ShipCustomerAddressID
	END
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

Open in new window

0
 

Author Comment

by:BrookK
ID: 24332119
Can someone tell me now how to modify my code to get this logic working?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24451721
Sorry, i didnt see the other question, getting lot of mails from EE :)
 

UPDATE IC
SET IC.POID = POLI.POID,
	IC.BillToAddressID = CASE WHEN PO.BillingAddressID IS NULL THEN QH.BillCustomerAddressID
	                          ELSE  PO.BillingAddressID	END,
	IC.ShipToAddressID = CASE WHEN PO.ShippingAddressID IS NULL THEN QH.ShipCustomerAddressID
	                          ELSE PO.ShippingAddressID END
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

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question