April 9, 2009 at 6:30 pm
Hi all.
I'm trying to apply a filter which could have multiple values based on a user's selection (@Report). The problem is that I'm trying to place all of this in the where clause without much luck.
The following doesn't work, but something like in the where clause here:
Select
fields,...
from
myTable
where
Report in
( Case
when @Report = 'TTT' then 'TTT'
when @Report = '370' then ('National', 'KLM', '777')
when @Report = '3000' then ('National', '3000')
End )
Thanks for any help,
Torres
April 9, 2009 at 6:50 pm
My advice is don't try and do it all in the where clause.
Use a table for the report classes....
CREATE TABLE ReportKeys(
report_key CHAR(10),
report_id CHAR(10)
)
GO
INSERT ReportKeys(report_key,report_id)
SELECT 'TTT','TTT' UNION ALL
SELECT '370','National' UNION ALL
SELECT '370', 'KLM' UNION ALL
SELECT '370', '777' UNION ALL
SELECT '3000','National' UNION ALL
SELECT '3000','3000'
GO
SELECT
fields,...
FROM
myTable
JOIN ReportKeys
ON myTable.Report = ReportKeys.report_id
WHERE ReportKeys.report_key=@Report
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 9, 2009 at 9:21 pm
Torres (4/9/2009)
Hi all.I'm trying to apply a filter which could have multiple values based on a user's selection (@Report). The problem is that I'm trying to place all of this in the where clause without much luck.
The following doesn't work, but something like in the where clause here:
Select
fields,...
from
myTable
where
Report in
( Case
when @Report = 'TTT' then 'TTT'
when @Report = '370' then ('National', 'KLM', '777')
when @Report = '3000' then ('National', '3000')
End )
Thanks for any help,
Torres
Modify the where clause like this:
WHERE 1 = CASE
WHEN @Report = 'TTT' AND Report = 'TTT' THEN 1
WHEN @Report = '370' AND Report In ('National', 'KLM', '777') THEN 1
WHEN @Report = '3000' AND Report In ('National', '3000') THEN 1
ELSE 0
END
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 9, 2009 at 9:24 pm
Torres (4/9/2009)
Hi all.I'm trying to apply a filter which could have multiple values based on a user's selection (@Report). The problem is that I'm trying to place all of this in the where clause without much luck.
The following doesn't work, but something like in the where clause here:
Select
fields,...
from
myTable
where
Report in
( Case
when @Report = 'TTT' then 'TTT'
when @Report = '370' then ('National', 'KLM', '777')
when @Report = '3000' then ('National', '3000')
End )
Thanks for any help,
Torres
Modify the where clause like this:
WHERE 1 = CASE
WHEN @Report = 'TTT' AND Report = 'TTT' THEN 1
WHEN @Report = '370' AND Report In ('National', 'KLM', '777') THEN 1
WHEN @Report = '3000' AND Report In ('National', '3000') THEN 1
ELSE 0
END
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 10, 2009 at 12:42 am
Jeffrey Williams (4/9/2009)
Modify the where clause like this:
WHERE 1 = CASE
WHEN @Report = 'TTT' AND Report = 'TTT' THEN 1
WHEN @Report = '370' AND Report In ('National', 'KLM', '777') THEN 1
WHEN @Report = '3000' AND Report In ('National', '3000') THEN 1
ELSE 0
END
Whilst I agree that modifying the CASE statement in this way will work, it is not scaleable - presumably more reports will be added and some removed at a later time....
Also, if you must hard-code the report groups in this way, ditch the CASE statement - it doesn't help...
WHERE
( @Report = 'TTT' AND Report='TTT')
OR ( @Report = '370' AND Report In ('National', 'KLM', '777') )
OR ( @Report = '3000' AND Report In ('National', '3000') )
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 10, 2009 at 9:49 am
mister.magoo (4/10/2009)
Jeffrey Williams (4/9/2009)
Modify the where clause like this:
WHERE 1 = CASE
WHEN @Report = 'TTT' AND Report = 'TTT' THEN 1
WHEN @Report = '370' AND Report In ('National', 'KLM', '777') THEN 1
WHEN @Report = '3000' AND Report In ('National', '3000') THEN 1
ELSE 0
END
Whilst I agree that modifying the CASE statement in this way will work, it is not scaleable - presumably more reports will be added and some removed at a later time....
Also, if you must hard-code the report groups in this way, ditch the CASE statement - it doesn't help...
WHERE
( @Report = 'TTT' AND Report='TTT')
OR ( @Report = '370' AND Report In ('National', 'KLM', '777') )
OR ( @Report = '3000' AND Report In ('National', '3000') )
Good catch - but, as in all things it really depends. I would definitely work out a way to categorize the report groups and incorporate that into the query instead of hardcoding it, but if you have to hardcode it I would try both constructs to see which gives you a better plan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 11, 2009 at 2:10 pm
I would follow Magoo's approach of making this table-driven for the sake of flexibility.
Might be a good idea to add an index to his ReportKeys table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 14, 2009 at 5:12 pm
Thanks for both of these methods!
It works great now.
Torres~
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply