SQLTeam.com | Weblogs | Forums

Populating second parameter with values chosen from the first param


#1

Hi Everyone,

I am trying to populate a parameter's fields by a multi value selection.. it works when I select 1 value from param1 but once I select 2 or more values I receive an error "An expression of non-boolean type specified in a context is expected. near ',' "
I checked numerous forums but with no luck...

my parameters (values from a query)

param1

select distinct
CASE
WHEN CHARINDEX('LINE-1', CT.Dim) > 0 THEN 'line1'
WHEN CHARINDEX('LINE-2', CT.Dim) > 0 THEN 'line2'
WHEN CHARINDEX('LINE-3', CT.Dim) > 0 THEN 'line3'
WHEN CHARINDEX('LINE-4', CT.Dim) > 0 THEN 'line4'
WHEN CHARINDEX('LINE-5', CT.Dim) > 0 THEN 'line5'
WHEN CHARINDEX('LINE-6', CT.Dim) > 0 THEN 'line6'
END AS 'Production_Line'

FROM ct.tables

param2

select DISTINCT

CASE
WHEN @line = 'line1' AND CHARINDEX('LINE-1', CT.Dim) > 0 AND CHARINDEX('MIXER', CT.Dim) > 0 THEN 'Mixer'
WHEN @line = 'line1' AND CHARINDEX('LINE-1', CT.Dim) > 0 AND CHARINDEX('LINKER', CT.Dim) > 0 THEN 'Linker'

WHEN @line = 'line2' AND CHARINDEX('LINE-2', CT.Dim) > 0 AND CHARINDEX('LINKER-1', CT.Dim) > 0 THEN 'Linker 1'
WHEN @line = 'line2' AND CHARINDEX('LINE-2', CT.Dim) > 0 AND CHARINDEX('LINKER-2', CT.Dim) > 0 THEN 'Linker 2'

WHEN @line = 'line3' AND CHARINDEX('LINE-3', CT.Dim) > 0 AND CHARINDEX('MIXER-2', CT.Dim) > 0 THEN 'Mixer 2'
WHEN @line = 'line3' AND CHARINDEX('LINE-3', CT.Dim) > 0 AND CHARINDEX('MIXER-3', CT.Dim) > 0 THEN 'Mixer 3'

WHEN @line = 'line4' AND CHARINDEX('LINE-4', CT.Dim) > 0 AND CHARINDEX('BLOCK-1', CT.Dim) > 0 THEN 'Block 1'
WHEN @line = 'line4' AND CHARINDEX('LINE-4', CT.Dim) > 0 AND CHARINDEX('MIXER-1', CT.Dim) > 0 THEN 'Mixer 1'

WHEN @line = 'line5' AND CHARINDEX('LINE-5', CT.Dim) > 0 AND CHARINDEX('BLOCK-1', CT.Dim) > 0 THEN 'Block 1'
WHEN @line = 'line5' AND CHARINDEX('LINE-5', CT.Dim) > 0 AND CHARINDEX('FORMER-1', CT.Dim) > 0 THEN 'Former 1'

WHEN @line = 'line6' AND CHARINDEX('LINE-6', CT.Dim) > 0 AND CHARINDEX('MICRO-1', CT.Dim) > 0 THEN 'Micro 1'
WHEN @line = 'line6' AND CHARINDEX('LINE-6', CT.Dim) > 0 AND CHARINDEX('MICRO-2', CT.Dim) > 0 THEN 'Micro 2'

END AS 'Equipment_Type'
END AS 'Equipment_Type'

FROM ct.tables

unfortunately only way I can pull this data is with char index's there are no primary keys since all the data is in one column

Any advice id appreciate it!

Thanks,
M


#2

figured it out

created 2 temporary tables and gave fake ID's to both !
if anyone wants more details let me know

Thanks

M