September 10, 2009 at 5:01 pm
Apologies if anyone just looked at this in the 2000 forum, it wasn't until I saw the Control Panel view in there i realised I'd posted there!
Anyway, I'm trying to produce a Case When statement that will allow me to select:
a) All Investors if @InvestorCode is null, i.e. User hasn't selected one
b) the specific Investor if the User has picked one
c) Several Investors as a group (declared in @Lookups) if the User selects one of the Groups names.
I can get the first two but fail on the sub-query returning more than one result (when I'm trying to get multiple results because it's feeding into an "InvestorCode IN (" statement! Anyone know how to return this?
declare @InvestorCode nvarchar(255)
declare @lookups table (description nvarchar(255),value nvarchar(255))
declare @unitstransacted table (investorcode nvarchar(255))
insert into @lookups (value, description)
select 'LGSATSCG','LGSA'
union all select 'LGSATSCG','LGSATS'
union all select 'LGSATSCT','LGSA'
union all select 'LGSATSCT','LGSATS'
union all select 'LGSATSDT','LGSA'
union all select 'LGSATSDT','LGSATS'
union all select 'LGSATSHG','LGSA'
union all select 'LGSATSHG','LGSATS'
union all select 'LGSATSTS','LGSB'
union all select 'LGSBTCBT','LGSB'
union all select 'LGSBTCBT','LGSBTC'
union all select 'LGSBTCCG','LGSB'
union all select 'LGSBTCCG','LGSBTC'
union all select 'LGSBTCCT','LGSB'
insert into @unitstransacted (investorcode)
select 'LGSATSCG'
union all select 'LGSATSCG'
union all select 'LGSATSCT'
union all select 'LGSATSCT'
union all select 'LGSATSDT'
union all select 'LGSATSDT'
union all select 'LGSATSHG'
union all select 'LGSATSHG'
union all select 'LGSATSTS'
union all select 'LGSBTCBT'
union all select 'LGSBTCBT'
union all select 'LGSBTCCG'
union all select 'LGSBTCCG'
union all select 'LGSBTCCT'
union all select 'LGSBTCBT'
union all select '1900'
union all select 'EISATSTS'
union all select '1773'
union all select '1760'
set @InvestorCode = 'LGSATSCT'
SELECT InvestorCode
FROM @UnitsTransacted
where investorcode in (
CASE WHEN ISNULL(@InvestorCode,'')= '' THEN InvestorCode
when @InvestorCode in (select description from @lookups) then (select value from @lookups where @InvestorCode = Description)
ELSE @InvestorCode END)
September 10, 2009 at 5:22 pm
Why do a CASE? If you are determined to go this route, why not just use OR?
Where InvestorCode is null OR investorCode = @investorCode
OR investorCode in (select ... from @lookups where ... = @investorcode)
You will get better performance if you segregated this into three queries something like this:
if @investorCode is null
begin
select (with no where clause)
end
else
begin
select
where investorCode = @investorCode
if @@rowcount = 0
begin
select
from
JOIN @lookups L on investorCode =
where L.... = @investorcode
end
end
Depending on how frequently the value will be a group or a single investorCode, you might choose to run the JOIN query to get a whole group before trying the single investor query, or use IF EXISTS() to see if the @investorCode is to be found in the lookups 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
September 10, 2009 at 6:41 pm
Thanks Bob! Certainly not determined to go any particular way, just hadn't had any earlier luck using an IF statement (plus I had attempted "rowcount" instead of "@@rowcount"!).
Have ended up changing the @@rowcount to a "if(select count(*)" to ensure that I only return one query (rather than displaying the blank one and then the subsequent correct one if one of the Groups is passed), and reused the "ISNULL(x,'') as I'm not sure if the program will be designed to pass null or ' ' (I'm not writing that) but thanks, I was really struggling with this one!
if ISNULL(@InvestorCode,'')= ''
begin
select investorcode from @unitstransacted
end
else
begin
if (select count(*) from @unitstransacted
where investorCode = @investorCode) = 0
begin
select value
from
@lookups where @InvestorCode = description
end
else
select investorcode from @unitstransacted
where investorCode = @investorCode
end
September 11, 2009 at 12:14 am
Problem's still solved, but in case this helps anyone else, I discovered later on this code couldn't be used as part of a wider "select * from X where investorcode in (ResultSet)", but inserting it into a Temp Table and calling that worked fine. E.g.
declare @tempInvestorGroupTable table (InvestorCode nvarchar(255))
if ISNULL(@InvestorCode,'')= ''
begin
insert into @tempInvestorGroupTable select investorcode from confirmed.unitstransacted
end
else
begin
if (select count(*) from confirmed.unitstransacted
where confirmed.unitstransacted.investorCode = @investorCode) = 0
begin
insert into @tempInvestorGroupTable select value from fn_GetLookups('Investor Groups')
where @InvestorCode = description
end
else
insert into @tempInvestorGroupTable select investorcode from confirmed.unitstransacted
where investorCode = @investorCode
end
and WHERE InvestorCodein (select investorcode from @tempInvestorGroupTable)
Thanks again, Bob!
September 11, 2009 at 7:37 am
No problem, Nathan. Happy to be of help. I'm sorry about the display of the blank row. Obviously, I just sort of threw that example together freehand.
Thank you very much for posting your final solution back here as well. It makes the circle complete and makes this post a better resource for anyone else who comes looking.
It's been a pleasure doing business with you 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 11, 2009 at 8:10 am
Nathan, Just keep in mind that using IF statement to figure out which Query to execute could cause parameter sniffing and might cause bad performance due to a bad plan.
I am not saying that it will, but there is a possibility.
-Roy
September 11, 2009 at 8:29 am
It can't happen to the first query because there is no WHERE clause. The second two queries are, or should be, mutually exclusive based on the IF logic. A bigger gun might overrule me but I would think we're safe. I thought each query's execution plan is developed the first time that query actually executes, and it won't execute without an appropriate parameter.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 11, 2009 at 9:14 am
Two quick comments first up:
NOT EXISTS is faster than (SELECT COUNT(*) FROM table) = 0
Have a look at Erland Sommarskog's article on dynamic searches. It has parallels with what you seem to be looking at doing, and in any case is pretty much required reading for the budding SQL expert!
Back in a bit.
Paul
edit: Ok, 3 comments...can I assume that fn_GetLookups('Investor Groups') references an in-line table-valued function? (rather than a multi-statement TVF)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 10:36 pm
Bob Hovious (9/11/2009)
No problem, Nathan. Happy to be of help. I'm sorry about the display of the blank row. Obviously, I just sort of threw that example together freehand.Thank you very much for posting your final solution back here as well. It makes the circle complete and makes this post a better resource for anyone else who comes looking.
It's been a pleasure doing business with you 😉
No worries, half of more my more advanced SQL knowledge comes from completed examples in here so I always make sure I post the complete solution if I get help 🙂
Paul White (9/11/2009)
Two quick comments first up:NOT EXISTS is faster than (SELECT COUNT(*) FROM table) = 0
Have a look at Erland Sommarskog's article on dynamic searches. It has parallels with what you seem to be looking at doing, and in any case is pretty much required reading for the budding SQL expert!
Back in a bit.
Paul
edit: Ok, 3 comments...can I assume that fn_GetLookups('Investor Groups') references an in-line table-valued function? (rather than a multi-statement TVF)
Thanks Paul, I'll throw in NOT EXISTS on Monday and give Erland's article a read.
Correct, fn_GetLookups is in-line. It hits the Lookup table that I partially reproduced with @Lookups (so that it could be built easily on here).
September 14, 2009 at 6:00 am
Guys, what do you think about the solution presented below:
SELECT * FROM @UnitsTransacted A
left outer join@lookups B
on A.investorcode=B.[value] and B.description=@InvestorCode
where A.investorcode= isnull(@InvestorCode,A.investorcode) or B.description=@InvestorCode
September 14, 2009 at 3:58 pm
ravi4work (9/14/2009)
Guys, what do you think about the solution presented below:SELECT * FROM @UnitsTransacted A
left outer join@lookups B
on A.investorcode=B.[value] and B.description=@InvestorCode
where A.investorcode= isnull(@InvestorCode,A.investorcode) or B.description=@InvestorCode
As "Select Distinct investorcode from @UnitsTransacted" (instead of *), yes, it does work. Top use of ISNULL there, I've not seen that before!
September 14, 2009 at 4:27 pm
NathanB (9/14/2009)
Top use of ISNULL there, I've not seen that before!
It doesn't perform well usually since use of the function forces an index scan instead of a seek (assuming a useful index exists).
See http://www.sommarskog.se/dyn-search-2005.html for a choice of better approaches.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 4:39 pm
No worries. Between that article and Was the same link, feel free to ignore this! http://www.sommarskog.se/dyn-search-2005.html, which I'm only about a quarter or the way through, I'm covered for reading material for a while 🙂
September 14, 2009 at 5:11 pm
Um.... aren't those both the same link?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2009 at 5:22 pm
D'oh! Still early in the morning for me, I didn't scroll up from where I was reading the first link (opened it to read later). Obviously ISNULL isn't covered in that first quarter that i've already read!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply