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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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