64194

Case Statement in SQL Query

Question:

I was trying to use case statement in the select statement like this in SQL server 2005 and i get the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Cant a Case be used inside SQL query?!

declare @TypeofDayID int set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID) Select CASE WHEN @TypeofDayID = 1 THEN (Select * from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID) WHEN @TypeofDayID = 2 THEN (Select * from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID) WHEN @TypeofDayID = 9 or @TypeofDayID = 10 THEN (Select * from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID) END from RepInfo RD

Answer1:

CASE isn't used for logical flow control... use IF / ELSE IF instead:

declare @TypeofDayID int set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID) IF @TypeofDayID = 1 Select * from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID ELSE IF @TypeofDayID = 2 Select * from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID ELSE IF @TypeofDayID = 9 or @TypeofDayID = 10 Select * from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID

Keep in mind... since you're using SELECT *, and joining to a different table based on @TypeOfDayID, you'll likely end up with a jagged result set, meaning that you'll have a varying number of columns based on which branch is taken.

This can be a pain to work with programmatically, so it would be a good idea to avoid SELECT * for this reason, as well as other reasons...

Answer2:

I think what you're trying to do can be better handled this way.

declare @TypeofDayID int set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID) IF @TypeofDayID = 1 Select * from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID ELSE IF @TypeofDayID = 2 Select * from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID ELSE IF @TypeofDayID = 9 or @TypeofDayID = 10 Select * from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID

Recommend

  • T-SQL - Minutes per hour between two datetimes
  • display unique field out of 10 records in sql
  • Variable manipulation in Oracle
  • Use iterator to call the non-static function in STL Set
  • How to find invalid Char in a SQL table
  • Why incrementing int primitive in while loop does not loop forever
  • TSQL remap big number to smaller, but keep Identity
  • how to handle large size of update query in mysql with laravel
  • Socket.io on socket disconnect
  • React JS 'this' not working as expected
  • Testing Rails module without loading ActiveRecord
  • MVC - Dynamically loading Partial Views
  • Bullet-Proof ACL using AngularJS
  • How to mock current date?
  • How to limit cursor movement in WPF based app?
  • Is it possible to disable certain weekdays in DatePickerDialog?
  • Avoiding dependencies is exploding the number of projects in my VS solution
  • PXAction seemingly does nothing
  • How to do popup on new site for 301 redirected users?
  • how to verify a P12 certificate using openssl?
  • JsonMappingException: Can not deserialize instance of java.lang.Integer out of START_OBJECT token
  • TextBox AutoCompleteStringCollection Suggest
  • keeping track of nested ng-repeat index's
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • RxJava debounce by arbitrary value
  • onBackPressed() not being executed
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • ActionScript 2 vs ActionScript 3 performance
  • How to make Safari send if-modified-since header?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How to pass list parameters for each object using Spring MVC?
  • How do you join a server to an Active Directory (domain)?
  • Why joiner is not used after Sequence generator or Update statergy
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • How does Linux kernel interrupt the application?
  • JaxB to read class hierarchy