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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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