August 9, 2010 at 3:11 am
Hi,
Can anyone tell me what is wrong in this query,I get the following error message,
Declare @timeZone varchar(10)
Declare @ZoneTime varchar(30)
Set @timeZone='CT'
Set @ZoneTime = CASE
WHEN (datepart(hour,dateadd(hh,dbo.GetTimeZoneDiff(@timeZone),getdate())) - datepart(hour,'00:00') < 12) THEN 'Morning'
WHEN (datepart(hour,dateadd(hh,dbo.GetTimeZoneDiff(@timeZone),getdate())) - datepart(hour,'00:00') < 17) THEN 'Afternoon'
WHEN (datepart(hour,dateadd(hh,dbo.GetTimeZoneDiff(@timeZone),getdate())) - datepart(hour,'00:00') < 24) THEN 'Evening'
END
print @ZoneTime
BEGIN
Select CASE @ZoneTime
WHEN 'Morning'
THEN
(
Select [wrk_ha_calllist].[id_CallListId] from [wrk_ha_calllist]
where [wrk_ha_calllist].[preferredtime]='Afternoon'
UNION
Select [wrk_ha_calllist].[id_CallListId] from [wrk_ha_calllist]
where [wrk_ha_calllist].[preferredtime]='Evening'
)
WHEN 'Afternoon'
THEN
(
Select [wrk_ha_calllist].[id_CallListId] from [wrk_ha_calllist]
where [wrk_ha_calllist].[preferredtime]='Morning'
UNION
Select [wrk_ha_calllist].[id_CallListId] from [wrk_ha_calllist]
where [wrk_ha_calllist].[preferredtime]='Afternoon'
)
WHEN 'Evening'
THEN
(
Select [wrk_ha_calllist].[id_CallListId] from [wrk_ha_calllist]
where [wrk_ha_calllist].[preferredtime]='Afternoon'
)
END
END
I get the following Error message,
Msg 512, Level 16, State 1, Line 14
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
August 9, 2010 at 3:23 am
Can you provide the below function code..
dbo.GetTimeZoneDiff(@timeZone)..??
[font="Comic Sans MS"]Praveen Goud[/font]
August 9, 2010 at 5:07 am
Declare @timeZone varchar(10)
Declare @ZoneTime varchar(30)
Set @timeZone='CT'
Set @ZoneTime = CASE
WHEN (datepart(hour,dateadd(hh,dbo.GetTimeZoneDiff(@timeZone),getdate())) - datepart(hour,'00:00') < 12) THEN 'Morning'
WHEN (datepart(hour,dateadd(hh,dbo.GetTimeZoneDiff(@timeZone),getdate())) - datepart(hour,'00:00') < 17) THEN 'Afternoon'
WHEN (datepart(hour,dateadd(hh,dbo.GetTimeZoneDiff(@timeZone),getdate())) - datepart(hour,'00:00') < 24) THEN 'Evening'
END
print @ZoneTime
IF @ZoneTime = 'Morning'
BEGIN
Select l.[id_CallListId]
from [wrk_ha_calllist] l
where l.[preferredtime] IN ('Afternoon', 'Evening')
END
IF @ZoneTime = 'Afternoon'
BEGIN
Select l.[id_CallListId]
from [wrk_ha_calllist] l
where l.[preferredtime] IN ('Morning', 'Afternoon')
END
IF @ZoneTime = 'Evening'
BEGIN
Select l.[id_CallListId]
from [wrk_ha_calllist] l
where l.[preferredtime] = 'Afternoon'
END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2010 at 8:34 am
The subquery in your case statement is returning more than 1 value. It looks like what you are trying to do is a conditional result set based on the value of @TimeZone?
Probably the easiest way to deal with this is to use some if statements instead of trying to do this in a select. Notice also that i changed the union to use a simple where clause.
if @TimeZone = 'Morning'
Select [wrk_ha_calllist].[id_CallListId] from [wrk_ha_calllist]
where [wrk_ha_calllist].[preferredtime] in ('Afternoon', 'Evening')
if @TimeZone = 'Afternoon'
select here
if @TimeZone = 'Evening'
select here
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply