Case statement

• varunkum

  Default port

  Points: 1419

  More actions

  January 7, 2011 at 10:47 am

  #388579

  select

  (CASE

  WHEN @ReportParameter1='apple' THEN (select DESCRIPTION from CIRC.TAG

  Where Rownum <= 10)

  WHEN @ReportParameter1='bat' THEN (select DESCRIPTION from CIRC.TAG)

  ELSE null

  END) Description

  from dual

  I get "single-row subquery returns more than one row". What's wrong in my sql.

  If not with case please suggest me any other way to write this.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 7, 2011 at 11:12 am

  #1270794

  Your "select description" piece is returning more than one row. You can have it do that in that part of the query.

  I'd have to see table definitions, at least, to suggest how to write this correctly.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 7, 2011 at 6:09 pm

  #1271000

  select DESCRIPTION from CIRC.TAG

  Where Rownum <= 10

  AND @ReportParameter1='apple'

  UNION ALL

  select DESCRIPTION from CIRC.TAG

  WHERE @ReportParameter1='bat'

  UNION ALL

  SELECT NULL

  WHERE @ReportParameter1 NOT IN ('bat' ,'apple')

  This will do the same job - you cannot select a set of rows inside a CASE statement, but in this case you can UNION all three options and the optimiser works it out and only executes the relevant branch.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]