Subquery in Case When that can produce multiple results for IN query

• Nugby

  SSCommitted

  Points: 1612

  More actions

  September 10, 2009 at 4:50 pm

  #209848

  CANCELLED, sorry, have moved this to the 2005 forum. Accidentally posted here

  Hey guys, 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'

  [p]

  set @InvestorCode = 'LGSATSCT'[/p]

  [p]

  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)[/p]

• Nugby

  SSCommitted

  Points: 1612

  More actions

  September 10, 2009 at 4:57 pm

  #1052028

  Sorry, just realised I put this in SQL 200 instead of 2005. My apologies!