Help with Subquery and variable

[soklear]

Hi all. I am recieving:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Below is the set statement I am using to get the above error.

set @abndless = (select new_time from tmphdr where new_time < 10)

I realize why I am getting the error (due to many values being returned from above set/select statement), but, can anyone help me with how I would set a variable or ???? to be able to retrieve all values in column new_time that are less then 10?

Thanks in advance.

Bob

 

[bdreed35]

The short answer is that you can't return more than 1 value into your variable.

Can you post more of you code so that we can see what you are trying to do with the variable? This will help with a work around to the probelm you have.

~Brian

 

[soklear]

Sure. Thank you for responding. I didnt want to assume someone would want to read all the code. Below is the rest of it. Please be advised that I am a novice, trying to teach myself.

I am trying to build a table/report that lists the values and totals from hour to hour in any given day. I need the values from (new_time) and only where (new_time < 10) and I need them totalled for each hour. I think I can build a table from the original Header table for each computation, but, isnt there an easier way?

Thanks again.

Bob
-- Begin SQL
-- I. Declare Time Variables
Declare @HStr varchar(20)
Declare @StartTime DateTime
Declare @EndTime DateTime
Declare @abndless INT
-- II.Time Period Variables
set @StartTime = '02-14-2004 00:00'
set @EndTime = '02-14-2004 23:59'
-- III.Create Tables for Report
Begin
Select top 0 * into tmphdr
From Header
End
insert into tmphdr
select * from Header
where start_time > @starttime
and end_time < @endtime
and DNIS is NOT Null
--
Create table abobstbl(
Hr varchar(20),
ttlcls int null,
abndless int null)
while dateadd(hour,1,@startTime) <= @EndTime
begin

set @HStr = convert(varchar,datepart hour,@startTime))+' - '+convert(varchar,datepart(hour,@startTime)+1)

set @abndless = (select new_time from tmphdr where
new_time < 10)

insert abobstbl
select @HStr, Count(DNIS), @abndless
from tmphdr
where
Start_time >@StartTime
and start_time <dateadd(hour,1,@startTime)

set @StartTime = dateadd(hour,1,@StartTime)
end

select 'Period ' = Hr,
'Number of Calls' = ttlcls,
'Abandon Less 10' = Abndless
from abobstbl
drop table tmphdr
drop table abobstbl

 

[SemperFiDownUnda]

Unless I'm missing something wouldn't this do what you want?

INSERT INTO abobstbl
SELECT convert(varchar,datepart hour,@startTime))+' - '+convert(varchar,datepart(hour,@startTime)+1),
       COUNT(1),
       New_time
  FROM tmphdr
 WHERE Start_time > @StartTime
   AND Start_time < dateadd(hour,1,@StartTime)
   AND new_time < 10

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first