[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Conditional CASE statements

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
BrookK
Asked:
BrookK
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
ezraaCommented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
BrookKAuthor 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,
	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
 
BrookKAuthor Commented:
Can someone tell me now how to modify my code to get this logic working?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now