Using case within a Join

Christopher Jack 1,611 Reputation points
2021-01-29T12:39:00.753+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

4 additional answers

Sort by: Most helpful
  1. Doug Bloch 66 Reputation points
    2021-01-29T13:24:49.653+00:00

    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
    
    1 person found this answer helpful.

  2. Guoxiong 8,126 Reputation points
    2021-01-29T14:35:25.077+00:00

    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
    
    1 person found this answer helpful.

  3. Guoxiong 8,126 Reputation points
    2021-01-29T17:03:45.09+00:00
    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  
    

    61917-image.png

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


  4. EchoLiu-MSFT 14,571 Reputation points
    2021-02-01T08:46:51.8+00:00

    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