CODEWITHSUNDEEP
sqljoincase-statement
PureData1
PureData1

Reputation: 71

CASE statement within a JOIN query in SQL

This query is to print the JOINED tables with the creditcard column showing "cash" for every row where there is no card name.

I wrote the following, but it results in error:

SELECT  st.Name Territoryname,pp.LastName SalesPerson,ps.Name ShipMethod,
        sc.CardType CreditCardType,
        soh.OrderDate,soh.TotalDue
FROM    Sales.SalesOrderHeader soh
JOIN    Person.Person pp
ON      soh.SalesPersonID=pp.BusinessEntityID
JOIN    Purchasing.ShipMethod ps
ON      ps.ShipMethodID=soh.ShipMethodID
JOIN    Sales.CreditCard sc (CASE WHEN sc.CardType='Distinguish' THEN 'Cash' ELSE sc.CardType     END),
ON      sc.CreditCardID=soh.CreditCardID
JOIN    Sales.SalesTerritory st
ON      st.TerritoryID=soh.TerritoryID

Upvotes: 0

Views: 1527

Answers (3)

shawtydubs
shawtydubs

Reputation: 64

Your CASE statement is in the wrong place. You want it in the SELECT portion

select st.name Territoryname, pp.LastName SalesPerson, ps.Name ShipMethod, soh.OrderDate, soh.TotalDue, 
CASE sc.CardType
WHEN null THEN 'Cash' 
ELSE sc.CardType
END
FROM Sales.SalesOrderHeader soh
JOIN Person.Person pp ON soh.SalesPersonID=pp.BusinessEntityID
JOIN Purchasing.ShipMethod ps ON ps.ShipMethodID=soh.ShipMethodID
JOIN Sales.CreditCard sc ON sc.CreditCardID=soh.CreditCardID
JOIN Sales.SalesTerritory st ON st.TerritoryID=soh.TerritoryID

You also mention that it should say 'Cash' when CardType is empty, so please note I changed it to when 'Null' in the select statement instead of when 'Distinguish'

Upvotes: 0

jpw
jpw

Reputation: 44881

Is this what you want perhaps?

SELECT  
       st.Name Territoryname,
       pp.LastName SalesPerson,
       ps.Name ShipMethod,
       CASE WHEN sc.CardType='Distinguish' THEN 'Cash' ELSE sc.CardType END AS CreditCardType,
       soh.OrderDate,
       soh.TotalDue
FROM    Sales.SalesOrderHeader soh
JOIN    Person.Person pp 
ON      soh.SalesPersonID=pp.BusinessEntityID
JOIN    Purchasing.ShipMethod ps 
ON      ps.ShipMethodID=soh.ShipMethodID
JOIN    Sales.CreditCard sc 
ON      sc.CreditCardID=soh.CreditCardID
JOIN    Sales.SalesTerritory st 
ON      st.TerritoryID=soh.TerritoryID

This will displaycashwhere the CardType isDistinguish. Maybe it should be:

CASE WHEN sc.CardType IS NULL THEN 'Cash' ELSE sc.CardType END AS CreditCardType

if you want to displaycashwhere the card type is missing (although I don't think this can happen as you are using inner joins, and not left joins).

Upvotes: 1

Z .
Z .

Reputation: 12837

you do not need the CASE in the join. instead put the case in the select only:

SELECT ..., 
   CASE WHEN CardType = '' THEN 'Cash' ELSE CardType END AS CardType,
   ...

also, if some of the joined tables will not have corresponding rows in case of a cash payment you'll need to use LEFT JOINs

Upvotes: 0

Related Questions