CODEWITHSUNDEEP
sqlsql-serversql-server-2012conditional-statements
jon.r
jon.r

Reputation: 1058

Selecting columns based on a case SQL

I'm wondering how I can return specific results depending on my first selected statement. Basically I have two IDs. CustBillToID and CustShipToID. If CustShipToID is not null I want to select that and all the records that are joined to it. If it is null default to the CustBillToID and all the results that are joined to that.

Here is my SQL that obviously doesn't work. I should mention I tried to do a sub query in the conditional, but since it returns multiple results it won't work. I am using SQL Server 2012. 

SELECT  CASE WHEN cp.CustShipToID IS NOT NULL 
                   THEN
                   cy.CustDesc,
                   cy.Address1,
                   cy.Address2,
                   cy.City,
                   cy.State,
                   cy.ZIP,
                   cy.Phone
                   ELSE
                   c.CustDesc,
                   c.Address1,
                   c.Address2,
                   c.City,
                   c.State,
                   c.ZIP,
                   c.Phone
                   END
                   LoadID,
                   cp.CustPOID,
                   cp.POBillToRef,
                   cp.POShipToRef,
                   cp.CustBillToID,
                   cp.CustShipToID,
                   cp.ArrivalDate,
                   cp.LoadDate,
                   cp.StopNum,
                   cp.ConfNum,
                   cp.EVNum,
                   cp.ApptNum,
                   ld.CarrId,
                   ld.Temperature,
                   cr.CarrDesc

 FROM [Sales].[dbo].[CustPO] AS cp
              LEFT OUTER JOIN Load AS ld
              ON cp.LoadID = ld.LoadID
              LEFT OUTER JOIN Carrier AS cr
              ON ld.CarrId = cr.CarrId
              LEFT OUTER JOIN Customer AS c
              ON c.CustId = cp.CustBillToID
              WHERE CustPOID=5213

Any ideas?

Also my current SQL is below, I do a conditional to determine if it's set. I'd rather do it in SQL if possible.

SELECT cp.LoadID,
                   cp.CustPOID,
                   cp.POBillToRef,
                   cp.POShipToRef,
                   cp.CustBillToID,
                   cp.CustShipToID,
                   cp.ArrivalDate,
                   cp.LoadDate,
                   cp.StopNum,
                   cp.ConfNum,
                   cp.EVNum,
                   cp.ApptNum,
                   ld.CarrId,
                   ld.Temperature,
                   cr.CarrDesc,
                   c.CustDesc as CustBillToDesc,
                   c.Address1 as CustBillAddress1,
                   c.Address2 as CustBillAddress2,
                   c.City as CustBillCity,
                   c.State as CustBillState,
                   c.ZIP as CustBillZIP,
                   c.Phone as CustBillPhone,
                   cy.CustDesc as CustShipToDesc,
                   cy.Address1 as CustShipAddress1,
                   cy.Address2 as CustShipAddress2,
                   cy.City as CustShipCity,
                   cy.State as CustShipState,
                   cy.ZIP as CustShipZIP,
                   cy.Phone as CustShipPhone

              FROM [Sales].[dbo].[CustPO] as cp
              left outer join Load as ld
              on cp.LoadID = ld.LoadID
              left outer join Carrier as cr
              on ld.CarrId = cr.CarrId
              left outer join Customer as c
              on c.CustId = cp.CustBillToID
              left outer join Customer as cy
              on cy.CustId = cp.CustShipToID
              WHERE CustPOID=?

Upvotes: 0

Views: 107

Answers (3)

Stephane BOISSEAU
Stephane BOISSEAU

Reputation: 86

Did you try coalesce(CustShipToID,CustBillToID ) ?

...
FROM [Sales].[dbo].[CustPO] as cp
          left outer join Load as ld
          on cp.LoadID = ld.LoadID
          left outer join Carrier as cr
          on ld.CarrId = cr.CarrId
          inner join Customer as c
          on c.CustId = coalesce(cp.CustShipToID,cp.CustBillToID )
...

Upvotes: 0

beauXjames
beauXjames

Reputation: 8418

For this, you basically want to build a string that is your SQL and then execute the string...look @ the answer to this one ::

SQL conditional SELECT

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You need a separate case for each column:

SELECT (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.CustDesc ELSE  c.CustDesc END) as CustDesc,
       (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.Address1 ELSE  c.Address1 END) as Address1,
       (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.Address2 ELSE  c.Address2 END) as Address2,
       (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.City ELSE  c.City END) as City,
       (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.State ELSE  c.State END) as State,
       (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.ZIP ELSE  c.ZIP END) as ZIP,
       (CASE WHEN cp.CustShipToID IS NOT NULL THEN cy.Phone ELSE  c.Phone END) as Phone,
       . . .

Upvotes: 4

Related Questions