Using the Name of a CASE STATEMENT later on in a CONCAT

  • I understand why i can't do this (using CONCAT with "MOT2".  For the same reason i couldn't use "FILER", but I was able to work around FILER . . . but can i work around my CASE statement and use MOT2 in a CONCAT?  (problem is in the last row before the FROM . . . . . thanks 
    SELECT Distinct
    H.Entry_Num as Entry
    ,Left(H.Entry_Num,3) as Filer
    ,H.Entry_Summary_Date
    ,H.Entered_value
    ,H.Total_ADDCVD
    ,H.Total_duty
    ,H.Total_MPF
    ,H.Total_HMF
    ,H.Importer
    ,H.Mot_Desc
    ,CASE
    WHEN H.MOT in ('10','11','40','41')
         THEN 'AIR/SEA'
    WHEN MOT IN ('20','21','30','31')
         THEN 'BORDER'
       END as MOT2
    ,concat(Left(H.Entry_Num,3),'-', MOT2)
    FROM
    ADHOC.ATS_ESH H
    WHERE
    H.Importer = 'BROKER'
    AND H.Entry_Summary_Date >= '2/1/2018'
    AND H.Entry_Summary_Date < '3/1/2018'

  • jeffshelix - Tuesday, March 20, 2018 8:56 AM

    I understand why i can't do this (using CONCAT with "MOT2".  For the same reason i couldn't use "FILER", but I was able to work around FILER . . . but can i work around my CASE statement and use MOT2 in a CONCAT?  (problem is in the last row before the FROM . . . . . thanks 
    SELECT Distinct
    H.Entry_Num as Entry
    ,Left(H.Entry_Num,3) as Filer
    ,H.Entry_Summary_Date
    ,H.Entered_value
    ,H.Total_ADDCVD
    ,H.Total_duty
    ,H.Total_MPF
    ,H.Total_HMF
    ,H.Importer
    ,H.Mot_Desc
    ,CASE
    WHEN H.MOT in ('10','11','40','41')
         THEN 'AIR/SEA'
    WHEN MOT IN ('20','21','30','31')
         THEN 'BORDER'
       END as MOT2
    ,concat(Left(H.Entry_Num,3),'-', MOT2)
    FROM
    ADHOC.ATS_ESH H
    WHERE
    H.Importer = 'BROKER'
    AND H.Entry_Summary_Date >= '2/1/2018'
    AND H.Entry_Summary_Date < '3/1/2018'

    Your CONCAT appears after the END of the CASE construction, so it's not part of that column's definition. What are you trying to do?
    On a separate note, to avoid all ambiguity and possible problems with different locales, literal dates are better expressed in 'YYYYMMDD' format.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks for the reply.  I want to concat the Filer (code) with the Mode of Transportation . . . .I will then put it in SSRS and create table to aggregate the money columns by FILER-MOT2.  
    The user wants to combine separate MOT Codes into 1 group . . . either AIR/SEA or BORDER.

    Maybe i should put all of this into a CTE, then concatenate later?

  • I think I see what you're trying to do but difficult to be sure without any sample data or anything to go off of. But I think you have 2 options.
    1. Use a CTE - which is probably more traditional approach
    2. The way I like to do these things is with the APPLY operators...see example with your code below:


    SELECT Distinct
    H.Entry_Num as Entry
    ,Left(H.Entry_Num,3) as Filer
    ,H.Entry_Summary_Date
    ,H.Entered_value
    ,H.Total_ADDCVD
    ,H.Total_duty
    ,H.Total_MPF
    ,H.Total_HMF
    ,H.Importer
    ,H.Mot_Desc
    -- ,CASE
    -- WHEN H.MOT in ('10','11','40','41')
    --  THEN 'AIR/SEA'
    --WHEN MOT IN ('20','21','30','31')
    --  THEN 'BORDER'
    -- END as MOT2
    ,MOT2.MOT2
    ,concat(Left(H.Entry_Num,3),'-', MOT2.MOT2)
    FROM
    ADHOC.ATS_ESH H
    --Build your case statement as a join
    cross apply(
                select
                CASE
                     WHEN H.MOT in ('10','11','40','41')
                         THEN 'AIR/SEA'
                     WHEN H.MOT IN ('20','21','30','31')
                         THEN 'BORDER'
                     END as MOT2
                )MOT2

    WHERE
    H.Importer = 'BROKER'
    AND H.Entry_Summary_Date >= '2/1/2018'
    AND H.Entry_Summary_Date < '3/1/2018'

  • jeffshelix - Tuesday, March 20, 2018 8:56 AM

    I understand why i can't do this (using CONCAT with "MOT2".  For the same reason i couldn't use "FILER", but I was able to work around FILER . . . but can i work around my CASE statement and use MOT2 in a CONCAT?  (problem is in the last row before the FROM . . . . . thanks 
    SELECT Distinct
    H.Entry_Num as Entry
    ,Left(H.Entry_Num,3) as Filer
    ,H.Entry_Summary_Date
    ,H.Entered_value
    ,H.Total_ADDCVD
    ,H.Total_duty
    ,H.Total_MPF
    ,H.Total_HMF
    ,H.Importer
    ,H.Mot_Desc
    ,CASE
    WHEN H.MOT in ('10','11','40','41')
         THEN 'AIR/SEA'
    WHEN MOT IN ('20','21','30','31')
         THEN 'BORDER'
       END as MOT2
    ,concat(Left(H.Entry_Num,3),'-', MOT2)
    FROM
    ADHOC.ATS_ESH H
    WHERE
    H.Importer = 'BROKER'
    AND H.Entry_Summary_Date >= '2/1/2018'
    AND H.Entry_Summary_Date < '3/1/2018'

    Pretty sure you will need to duplicate your CASE in the CONCAT to achieve your goal.  Or, you could do this:

    WITH base AS (
    SELECT DISTINCT
      H.Entry_Num as Entry
      ,Left(H.Entry_Num,3) as Filer
      ,H.Entry_Summary_Date
      ,H.Entered_value
      ,H.Total_ADDCVD
      ,H.Total_duty
      ,H.Total_MPF
      ,H.Total_HMF
      ,H.Importer
      ,H.Mot_Desc
      ,CASE WHEN H.MOT in ('10','11','40','41')
         THEN 'AIR/SEA'
            WHEN MOT IN ('20','21','30','31')
         THEN 'BORDER'
       END as MOT2
    FROM
      ADHOC.ATS_ESH H
    WHERE
      H.Importer = 'BROKER'
      AND H.Entry_Summary_Date >= '20180201' -- '2/1/2018'
      AND H.Entry_Summary_Date <  '20180301' -- '3/1/2018'
    )
    SELECT
      .[Entry]
      , .[Filer]
      , .[Entry_Summary_Date]
      , .[Entered_value]
      , .[Total_ADDCVD]
      , .[Total_duty]
      , .[Total_MPF]
      , .[Total_HMF]
      , .[Importer]
      , .[Mot_Desc]
      , .[MOT2]
      , CONCAT(.[Filer],3),'-', .[MOT2])
    FROM
      base AS ;

  • Awesome! Works as entered!  Solved, thanks! 

    I

  • I prefer to use CROSS APPLY to assign an alias name, like below.  Just to show the capability, I also used a CROSS APPLY to get the CONCAT'd column value: that is, you can use a CROSS APPLY alias in a subsequent CROSS APPLY: HOW SWEET IS THAT!


    SELECT Distinct
    ...
    ,Alias1.MOT2
    ,Alias2.New_Column /* or concat(Left(H.Entry_Num,3),'-', Alias1.MOT2), as before */
    FROM
    ADHOC.ATS_ESH H
    CROSS APPLY (
      SELECT CASE
        WHEN H.MOT in ('10','11','40','41')
         THEN 'AIR/SEA'
        WHEN MOT IN ('20','21','30','31')
         THEN 'BORDER'
        END as MOT2
    ) AS Alias1
    CROSS APPLY (
        SELECT concat(Left(H.Entry_Num,3),'-', Alias1.MOT2) AS New_Column
    ) AS Alias2

    WHERE
    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher - Tuesday, March 20, 2018 9:57 AM

    I prefer to use CROSS APPLY to assign an alias name, like below.  Just to show the capability, I also used a CROSS APPLY to get the CONCAT'd column value: that is, you can use a CROSS APPLY alias in a subsequent CROSS APPLY: HOW SWEET IS THAT!


    SELECT Distinct
    ...
    ,Alias1.MOT2
    ,Alias2.New_Column /* or concat(Left(H.Entry_Num,3),'-', Alias1.MOT2), as before */
    FROM
    ADHOC.ATS_ESH H
    CROSS APPLY (
      SELECT CASE
        WHEN H.MOT in ('10','11','40','41')
         THEN 'AIR/SEA'
        WHEN MOT IN ('20','21','30','31')
         THEN 'BORDER'
        END as MOT2
    ) AS Alias1
    CROSS APPLY (
        SELECT concat(Left(H.Entry_Num,3),'-', Alias1.MOT2) AS New_Column
    ) AS Alias2

    WHERE
    ...

    More than one way to skin a cat.  Test and select the method that works (performs and scales) the best.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply