Help me in executing this CASE Statement

Question

Help me in executing this CASE Statement

GA_SQL

Hall of Fame

Points: 3166
More actions
April 26, 2016 at 6:11 am

#310194

Please let me know way to work this one or any alternative way to achieve it..Thanks in advance
SELECT
CASE
WHEN (SELECT ShiftType=2 FROM dbo.ShiftScheduler) IS NOT NULL THEN (SELECT FromDate,enddate,Shift,ShiftType FROM dbo.ShiftScheduler WHERE EndDate between '2016-04-01' and '2016-04-24' and Emp_Code='2414')
WHEN (SELECT ShiftType=1 FROM dbo.ShiftScheduler) IS NOT NULL THEN (SELECT Shift,ShiftType FROM Ihsmaster_delop.dbo.emploee WHERE empcode='2414')
ELSE 'NO SUCH EMP'
END AS SHIFT
Error
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What is this even trying to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

ganapathy.arvindan (4/26/2016)
Please let me know way to work this one or any alternative way to achieve it..Thanks in advance
SELECT
CASE
WHEN (SELECT ShiftType=2 FROM dbo.ShiftScheduler) IS NOT NULL THEN (SELECT FromDate,enddate,Shift,ShiftType FROM dbo.ShiftScheduler WHERE EndDate between '2016-04-01' and '2016-04-24' and Emp_Code='2414')
WHEN (SELECT ShiftType=1 FROM dbo.ShiftScheduler) IS NOT NULL THEN (SELECT Shift,ShiftType FROM Ihsmaster_delop.dbo.emploee WHERE empcode='2414')
ELSE 'NO SUCH EMP'
END AS SHIFT
Error
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

This will not work as you are trying to output multiple columns from a single case statement. Case statements are not control flow statements.

😎

SELECT

CASE

WHEN SS.ShiftType = 2 THEN [SINGLE COLUMN QUERY]

WHEN SS.ShiftType = 1 THEN [SINGLE COLUMN QUERY]

END

FROM dbo.ShiftScheduler SS

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

I was just about to question your 'steroids' comment, Eirikur, when I noticed it had been edited out!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Wild shot in the dark.

SELECT

FromDate,

enddate,

Shift,

ShiftType

FROM dbo.ShiftScheduler

WHERE EndDate BETWEEN '20160401' AND '20160424'

AND Emp_Code='2414'

AND ShiftType=2

UNION ALL

SELECT

NULL,

Shift,

ShiftType

FROM Ihsmaster_delop.dbo.emploee

WHERE empcode='2414'

AND EXISTS(SELECT ShiftType=1 FROM dbo.ShiftScheduler);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Phil Parkin (4/26/2016)
I was just about to question your 'steroids' comment, Eirikur, when I noticed it had been edited out!

Didn't mean to post that in the first place, hit post instead of preview while pondering on how to explain the difference. ( Win10 tablets can be tricky )

😎

ScottPletcher SSC Guru Points: 101120 More actions · Answer 6

IF EXISTS(SELECT 1 FROM dbo.ShiftScheduler WHERE ShiftType=2 AND Emp_Code='2414')

SELECT FromDate,enddate,Shift,ShiftType

FROM dbo.ShiftScheduler

WHERE ShiftType = 2 AND EndDate between '2016-04-01' and '2016-04-24' and Emp_Code='2414'

ELSE

IF EXISTS(SELECT 1 FROM dbo.ShiftScheduler WHERE ShiftType=1 AND Emp_Code='2414')

SELECT Shift,ShiftType

FROM Ihsmaster_delop.dbo.emploee

WHERE ShiftType = 1 AND empcode='2414'

ELSE

SELECT 'NO SUCH EMP' AS SHIFT

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

GA_SQL Hall of Fame Points: 3166 More actions · Answer 7

Thanks for all your reply...My output concern is from date '2016-04-01','2016-04-24' it has to display all the dates shift type..output sample

Date Shift shifttype

2016-04-01 1 1

2016-04-02 1 1

2016-04-03 1 1

2016-04-04 1 1

2016-04-05 2 4

2016-04-06 2 2

2016-04-07 1 1

2016-04-08 2 4

it should continue still 2016-04-24...Please help

GA_SQL Hall of Fame Points: 3166 More actions · Answer 8

For Achieving the above output i tried like this

ALTER PROCEDURE Emp_Shift1

@empcode varchar(50),

@FromDate datetime,

@EndDate datetime

AS

BEGIN

DECLARE @TotDays INT

DECLARE @CNT INT

SET @TotDays = DATEDIFF(DD,@FromDate,@EndDate)-- [NO OF DAYS between two dates]

PRINT @TotDays

SET @CNT = 0

WHILE @TotDays >= @CNT -- repeat for all days

BEGIN

IF EXISTS(SELECT ShiftType=2 FROM dbo.ShiftScheduler )

BEGIN

SELECT FromDate,enddate,Shift,ShiftType,Emp_Code FROM dbo.ShiftScheduler WHERE EndDate between @FromDate and @EndDate AND Emp_Code=@empcode ORDER BY EndDate

END

ELSE

IF EXISTS (SELECT ShiftType=1 FROM dbo.ShiftScheduler)

BEGIN

SELECT empcode,Shift,ShiftType FROM dbo.emploee where empcode=@empcode

END

ELSE PRINT 'NO SUCH EMP'

SET @CNT = @CNT + 1

END

Ending result like

2016-04-01 00:00:00.0002016-04-03 00:00:00.00024

Like this for 24 times... please help