Local variable with more than 1 value

Paddington · August 3, 2022, 10:59am

Hello experts,
my question is simply:
how can you assign 2 values to a variable and make the sql below work?
please note that i have declared the variable at the top of the code and assigned it 1 value however i would like it to take more than one value as you can see in the comment right next to the actual declare. You will see that the actual variable @Cat is used twice in the sql.

DECLARE @Cat1 varchar(3) = 'LSS'; --('LSS','LBS')

-- Source Mapping
with stage1 as (
select distinct 'WASP' "Source System",
ac.DESCRIPT "Category Description",
dl.CATEGORY "Category Code",
case when dl.FIELD_PROMPT is not NULL
then dl.FIELD_PROMPT
else dc.DESCRIPT
end "Attribute Name",
'WASP.EMS_ASSET_DETAILS' "Table",
'DETAIL_TEXT' "Field Name",
--NULL "Sample Data",
case when dc.DATA_TYPE = 'D'
then 'Date'
else case when (dl.FIELD_STYLE = '0' or dl.FIELD_STYLE is NULL)
then 'String'
else 'Enumerator'
end
end "Data Type",
case when d.REQUIRED = '-1'
then 'Y'
else 'N'
end "Mandatory",
dl.DETAIL_CODE "Detail Code",
case when (dl.FIELD_STYLE <> '0' and dl.FIELD_STYLE is not NULL and dc.REFERENCE_TABLE is NULL)
then 'AD_LOOKUP'
else dc.REFERENCE_TABLE
end "Reference Table",
case when (dl.FIELD_STYLE <> '0' and dl.FIELD_STYLE is not NULL and dc.REFERENCE_TABLE is NULL)
then 'L_CODE'
else dc.REF_KEYCOL
end "Reference Table Column",
case when (dl.FIELD_STYLE <> '0' and dl.FIELD_STYLE is not NULL and dc.REFERENCE_TABLE is NULL)
then concat('DETAIL_CODE = ',CHAR(39),dl.DETAIL_CODE,CHAR(39))
else dc.REF_CRITERIA
end "Reference Criteria"
from [WASP].[EMS_DETAIL_LAYOUT_curr_VW] dl
left join [WASP].[EMS_ASSET_CATEGORY_curr_VW] ac on dl.CATEGORY = ac.CATEGORY
left join [WASP].[EMS_DETAIL_CODE_curr_VW] dc on dl.DETAIL_CODE = dc.DETAIL_CODE
left join [WASP].[EMS_DETAILS_curr_VW] d on dl.DETAIL_CODE = d.DETAIL_CODE and dl.CATEGORY = d.CATEGORY
where dl.CATEGORY = @Cat1 /* Load Break Switch */ --- change this depending on UoM
and dl.GROUP_NO not in ('22','40') -- omit obsolete Legacy and unused tabs in WASP
),
stage2 as (
select ad.DETAIL_CODE "Detail Code",
max(al.DESCRIPT) "Sample Data1",
max(ad2.DETAIL_TEXT) "Sample Data2"
from [WASP].[EMS_ASSET_curr_VW] a
left join [WASP].[EMS_ASSET_DETAILS_curr_VW] ad on a.ASSET_ID = ad.ASSET_ID
left join [WASP].[EMS_AD_LOOKUP_curr_VW] al on ad.DETAIL_TEXT = al.L_CODE
and ad.DETAIL_CODE = al.DETAIL_CODE and al.DESCRIPT not in ('Unknown','unkown')
left join [WASP].[EMS_ASSET_DETAILS_curr_VW] ad2 on a.ASSET_ID = ad2.ASSET_ID
and ad.DETAIL_CODE = ad2.DETAIL_CODE and ad2.DETAIL_TEXT not in ('UNKNOWN','Unknown','unkown')
where a.CATEGORY = @Cat1 -- change according to UoM
and a.SERVICE_STATUS = 'I'
group by ad.DETAIL_CODE
),
stage3 as (
select s.[Detail Code], coalesce(s.[Sample Data1], s.[Sample Data2])sample_data
from stage2 s
)
select s1.[Source System], s1.[Category Description], s1.[Category Code], s1.[Attribute Name]
, s1.[Table], s1.[Field Name], s3.sample_data, s1.[Data Type], s1.[Mandatory], s1.[Detail Code]
, s1.[Reference Table], s1.[Reference Table Column], s1.[Reference Criteria]
from stage1 s1
left join stage3 s3 on s1.[Detail Code] = s3.[Detail Code]
order by 2

Any suggestions welcome.
thank you!

harishgg1 · August 3, 2022, 11:23am

one idea is CASE statement

case When Cat = 'LSS' then 'aa' When Cat = 'LBS' then 'bb'

Ifor · August 3, 2022, 12:04pm

DECLARE @Cat1s TABLE
(
	Cat1 varchar(10) NOT NULL PRIMARY KEY
);
INSERT INTO @Cat1s
VALUES ('LSS'),('LBS');

-- ...

-- where dl.CATEGORY = @Cat1
WHERE dl.CATEGORY IN (SELECT Cat1 FROM @Cat1s)

Paddington · August 3, 2022, 9:59pm

hi @Ifor ,
looks good but i am getting an error. see below:
Msg 103010, Level 16, State 1, Line 3
Parse error at line: 1, column: 16: Incorrect syntax near 'TABLE'.

thanks for the effort so far.

yosiasz · August 3, 2022, 11:02pm

Are you microsoft sql server? Go with this instead

Create table ##Cat1s
(
	Cat1 varchar(10) NOT NULL PRIMARY KEY
);
INSERT INTO @Cat1s
VALUES ('LSS'),('LBS');

Paddington · August 3, 2022, 11:37pm

hi @yosiasz ,
thanks for the reply. Yes I am using MS SQL Server 2018.
I changed the syntax according to your suggestion and am still getting the error below:

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 14, column: 13: Incorrect syntax near '@Cat1s'.

eventually it will work.
please keep suggestions coming in.
thank you all.

yosiasz · August 3, 2022, 11:48pm

Oops change it to ##
Don't just copy pasta
Understand first

Paddington · August 4, 2022, 2:19am

no worries.
thanks everyone for the help. I have found a way around it.
SQL below if interested.

--Get the Detail_Code values into a temp table

select CATEGORY, DETAIL_CODE, GROUP_NO, FIELD_PROMPT, FIELD_STYLE
into
#cat
from [WASP].[EMS_DETAIL_LAYOUT_curr_VW]
where CATEGORY in ('P')--('LSS','LBS')
and GROUP_NO not in ('22','40')
;

--Now for the main query (or the few tables haha )

-- Source Mapping
with stage1 as (
select distinct 'WASP' "Source System",
ac.DESCRIPT "Category Description",
dl.CATEGORY "Category Code",
case when dl.FIELD_PROMPT is not NULL
then dl.FIELD_PROMPT
else dc.DESCRIPT
end "Attribute Name",
'WASP.EMS_ASSET_DETAILS' "Table",
'DETAIL_TEXT' "Field Name",
--NULL "Sample Data",
case when dc.DATA_TYPE = 'D'
then 'Date'
else case when (dl.FIELD_STYLE = '0' or dl.FIELD_STYLE is NULL)
then 'String'
else 'Enumerator'
end
end "Data Type",
case when d.REQUIRED = '-1'
then 'Y'
else 'N'
end "Mandatory",
dl.DETAIL_CODE "Detail Code",
case when (dl.FIELD_STYLE <> '0' and dl.FIELD_STYLE is not NULL and dc.REFERENCE_TABLE is NULL)
then 'AD_LOOKUP'
else dc.REFERENCE_TABLE
end "Reference Table",
case when (dl.FIELD_STYLE <> '0' and dl.FIELD_STYLE is not NULL and dc.REFERENCE_TABLE is NULL)
then 'L_CODE'
else dc.REF_KEYCOL
end "Reference Table Column",
case when (dl.FIELD_STYLE <> '0' and dl.FIELD_STYLE is not NULL and dc.REFERENCE_TABLE is NULL)
then concat('DETAIL_CODE = ',CHAR(39),dl.DETAIL_CODE,CHAR(39))
else dc.REF_CRITERIA
end "Reference Criteria"
from #cat dl
left join [WASP].[EMS_ASSET_CATEGORY_curr_VW] ac on dl.CATEGORY = ac.CATEGORY
left join [WASP].[EMS_DETAIL_CODE_curr_VW] dc on dl.DETAIL_CODE = dc.DETAIL_CODE
left join [WASP].[EMS_DETAILS_curr_VW] d on dl.DETAIL_CODE = d.DETAIL_CODE and dl.CATEGORY = d.CATEGORY
),
stage2 as (
select ad.DETAIL_CODE "Detail Code",
max(al.DESCRIPT) "Sample Data1",
max(ad2.DETAIL_TEXT) "Sample Data2"
from [WASP].[EMS_ASSET_curr_VW] a
left join [WASP].[EMS_ASSET_DETAILS_curr_VW] ad on a.ASSET_ID = ad.ASSET_ID
left join [WASP].[EMS_AD_LOOKUP_curr_VW] al on ad.DETAIL_TEXT = al.L_CODE
and ad.DETAIL_CODE = al.DETAIL_CODE and al.DESCRIPT not in ('Unknown','unkown')
left join [WASP].[EMS_ASSET_DETAILS_curr_VW] ad2 on a.ASSET_ID = ad2.ASSET_ID
and ad.DETAIL_CODE = ad2.DETAIL_CODE and ad2.DETAIL_TEXT not in ('UNKNOWN','Unknown','unkown')
where a.CATEGORY IN (SELECT CATEGORY FROM #cat) -- change according to UoM
and a.SERVICE_STATUS = 'I'
group by ad.DETAIL_CODE
),
stage3 as (
select s.[Detail Code], coalesce(s.[Sample Data1], s.[Sample Data2])sample_data
from stage2 s
)
select s1.[Source System], s1.[Category Description], s1.[Category Code], s1.[Attribute Name]
, s1.[Table], s1.[Field Name], s3.sample_data, s1.[Data Type], s1.[Mandatory], s1.[Detail Code]
, s1.[Reference Table], s1.[Reference Table Column], s1.[Reference Criteria]
from stage1 s1
left join stage3 s3 on s1.[Detail Code] = s3.[Detail Code]
order by 2

--Finally tidy up the temp table

If(OBJECT_ID('tempdb..#cat') Is Not Null)
Begin
Drop Table #cat
End