Using case within a Join

Question

Hi,

I have a table called

HsCodes
hsCodes, Descriptions
01, food
0123344, spaghetti

In my other table Product I have a field called tarriffcode.

I am wanting to do a case on a the join so that if tarrifcode matches HsCodes then use that one else
take the first two numbers from tarrifcode and join on that.

I have

 LEFT JOIN [dbo].[DimHSCodes] hs 
 ON case ci.tariff_code = hs.HsCode THEN ci.tariff_code = hs.HsCode
 else LEFT(ci.tariff_code, 2) = hs.HsCode
 END

Full code is

   SELECT
     ci.tariff_code as 'ItemHSCode',
     hs.hsDescription as 'Description',
     ci.CountryOfOrigin as 'ItemCountryOfOrigin',
     sum(ci.QTY) as 'ItemQuantity',
     'EACH' as 'Unit Type',
     ci.Currency_code as 'Currency',
     CASE
     WHEN ci.KIT_PRODUCT = 1 THEN (sum(sol.SaleValueNetCur) / sum(ci.kit_row_qty)) / sum(ci.QTY)
     ELSE sum(sol.SaleValueNetCur) / sum(ci.QTY)
     END as 'ItemUnitPrice',
     CASE
     WHEN ci.KIT_PRODUCT = 1 THEN sum(sol.SaleValueNetCur) / sum(ci.kit_row_qty)
     ELSE sum(sol.SaleValueNetCur)
     END as 'Amount'
    FROM 
     [ZoomBI].[dbo].[Palletinfo] pal
     LEFT JOIN #DHLInfo ci
     ON pal.Parcel_Id = ci.Package_Id
     LEFT JOIN [dbo].[DimClientInfo] client
     ON ci.Warehouse_Code = client.Warehouse_code
     LEFT JOIN [Chalgrove].[FactSalesOrderLine] sol
     ON CAST(ci.sales_document_num as nvarchar) = cast(sol.OrderNumber as nvarchar)
     and ci.sales_item_Num = sol.SalesLine
     LEFT JOIN [dbo].[DimHSCodes] hs 
     ON case ci.tariff_code = hs.HsCode then ci.tariff_code = hs.HsCode
     else LEFT(ci.tariff_code, 2) = hs.HsCode
     END
    WHERE 
     CAST(pal.Date_Upload AS DATE) = CAST(GETDATE()-2 AS DATE)
     and
     ci.despatch_NUM IS NOT NULL
    GROUP BY
     ci.tariff_code,
     hs.hsDescription,
     ci.CountryOfOrigin,
     ci.Currency_code,
     ci.KIT_PRODUCT
    ORDER BY 
     ci.tariff_code

Though it doesnt like the "="

Sample Data

HsCode HsDescription
15 Vegetable Products
16 Foodstuffs
17 Foodstuffs
1509108010 Processed Food
1601009999 Processed Food
1602100000 Processed Food
1604201090 Processed Food
1704905100 Processed Food
1704905100 Processed Food
1704906500 Confectionery
1704906500 Confectionery

HcTarrifcode
1704906500
null

Not really sure how to go about that.. help appreciated.

Answer 1

The way round it was to join the table twice, and then do a case if NULL within the SELECT part.

Answer 2

ChristopherJack, ok with the full query here is the edited part of the query, that should work for you.

         LEFT JOIN [dbo].[DimHSCodes] hs 
             ON case WHEN ci.tariff_code = hs.HsCode then ci.tariff_code
             else LEFT(ci.tariff_code, 2)
             END = hs.HsCode

Answer 3

Try this:

LEFT JOIN [dbo].[DimHSCodes] hs 
    ON hs.HsCode = CASE WHEN ci.tariff_code = hs.HsCode THEN ci.tariff_code ELSE LEFT(ci.tariff_code, 2) END

Answer 4

DECLARE @HsCode TABLE (HsCode varchar(10));  
INSERT INTO @HsCode VALUES  
('15'),('16'),('17'),('1509108010'),('1601009999'),('1704906500');  
  
DECLARE @HcTarrifcode TABLE (HcTarrifcode varchar(10));  
INSERT INTO @HcTarrifcode VALUES  
('1704906500'),(NULL);  
  
SELECT *  
FROM @HcTarrifcode AS t1  
LEFT JOIN @HsCode AS t2 ON t2.HsCode = CASE WHEN t1.HcTarrifcode = t2.HsCode THEN t1.HcTarrifcode ELSE LEFT(t1.HcTarrifcode, 2) END  

You can filter out NULL if you do not want it in the output.

Answer 5

Do you have any update?

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo