CODEWITHSUNDEEP
sql-servercase
Jen Shanahan
Jen Shanahan

Reputation: 29

SQL Query with multiple CASE statements using the same field as THEN

Any idea why my case statement for UOMC.ToUnitOfMeasure returns NULL? If I reverse and show the To field before the from field, to will work but from will not.

SELECT 
    ECORESPRODUCT.DISPLAYPRODUCTNUMBER, 
    ECORESPRODUCT.SEARCHNAME, 
    INVENTTABLE.ITEMID, 
    UOMC.NUMERATOR,
    UOMC.DENOMINATOR, 
    CASE
       WHEN UOMC.FROMUNITOFMEASURE = UOM.RECID
         THEN UOM.SYMBOL
         ELSE 'NULL'
    END AS 'FROM UNIT',
    CASE
       WHEN UOMC.TOUNITOFMEASURE = UOM.RECID
         THEN UOM.SYMBOL
       ELSE 'NULL'
    END AS 'TO UNIT'
FROM 
    ECORESPRODUCT
JOIN 
    INVENTTABLE ON INVENTTABLE.RECID = ECORESPRODUCT.RECID
JOIN 
    UNITOFMEASURECONVERSION AS UOMC ON UOMC.PRODUCT =  ECORESPRODUCT.RECID
JOIN 
    UNITOFMEASURE AS UOM ON UOM.RECID = UOMC.FROMUNITOFMEASURE
WHERE 
    INVENTTABLE.NAMEALIAS <> 'Discontinued' 
    AND INVENTTABLE.COSTGROUPID = 'FG'

Upvotes: 0

Views: 85

Answers (2)

Jen Shanahan
Jen Shanahan

Reputation: 29

This ended up being the correct syntax. Was missing the extra join needed to match the TO unit and the FROM. SELECT ECORESPRODUCT.DISPLAYPRODUCTNUMBER, ECORESPRODUCT.SEARCHNAME, INVENTTABLE.ITEMID, UOMC.NUMERATOR, UOMC.DENOMINATOR, CASE WHEN UOMC.FROMUNITOFMEASURE = UOM_FROM.RECID THEN UOM_FROM.SYMBOL END AS 'FROM UNIT', CASE WHEN UOMC.TOUNITOFMEASURE = UOM_TO.RECID THEN UOM_TO.SYMBOL END AS 'TO UNIT' FROM ECORESPRODUCT JOIN INVENTTABLE ON INVENTTABLE.RECID = ECORESPRODUCT.RECID INNER JOIN UNITOFMEASURECONVERSION AS UOMC ON UOMC.PRODUCT = ECORESPRODUCT.RECID INNER JOIN UNITOFMEASURE AS UOM_FROM ON UOM_FROM.RECID = UOMC.FROMUNITOFMEASURE INNER JOIN UNITOFMEASURE AS UOM_TO ON UOM_TO.RECID = UOMC.TOUNITOFMEASURE WHERE INVENTTABLE.NAMEALIAS <> 'Discontinued' AND INVENTTABLE.COSTGROUPID = 'FG'

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

You have two references (TO and FROM) so you have to refer your directory UNITOFMEASURE twice:

SELECT
  ...
  UOM_FROM.SYMBOL as 'FROM UNIT',
  UOM_TO.SYMBOL as 'TO UNIT'
FROM
...
INNER JOIN UNITOFMEASURECONVERSION AS UOMC ON UOMC.PRODUCT =  ECORESPRODUCT.RECID
INNER JOIN UNITOFMEASURE AS UOM_FROM ON UOM_FROM.RECID = UOMC.FROMUNITOFMEASURE
INNER JOIN UNITOFMEASURE AS UOM_TO ON UOM_TO.RECID = UOMC.TOUNITOFMEASURE

Upvotes: 1

Related Questions