March 20, 2018 at 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'
March 20, 2018 at 9:17 am
jeffshelix - Tuesday, March 20, 2018 8:56 AMI 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 . . . . . thanksSELECT 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2018 at 9:21 am
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?
March 20, 2018 at 9:27 am
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'
March 20, 2018 at 9:28 am
jeffshelix - Tuesday, March 20, 2018 8:56 AMI 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 . . . . . thanksSELECT 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 ;
March 20, 2018 at 9:50 am
Awesome! Works as entered! Solved, thanks!
I
March 20, 2018 at 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
...
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".
March 20, 2018 at 10:18 am
ScottPletcher - Tuesday, March 20, 2018 9:57 AMI 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