June 7, 2010 at 7:16 pm
I have the below code and it is returning error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Could someone please help me:unsure:
IF (@date > CAST( CONVERT( varchar(20), GETDATE(), 101 ) AS datetime ))
BEGIN
SET @Date = CAST( CONVERT( varchar(20), GETDATE(), 101 ) AS datetime )
END
declare @workperiods table
(WorkPeriodid int,
StartDate datetime,
EndDate datetime,
WorkScheduleID int)
insert into @workperiods
(WorkPeriodid,
StartDate,
EndDate,
WorkScheduleID)
select
WorkPeriodid,
StartDate,
EndDate,
WorkScheduleID
from
integrateprod.dbo.sysworkperiods (nolock)
where
StartDate <=@date
and
EndDate >=@date
--MGT
--Added a table to contain all possible client ids, as a 'or' statement on the where clause was causing problems with speed
Select
b.Branch, cb.branchdescription, cb.branchid, b.jobtitle, regioncode, reportstoname,
[Stage_desc] = (case
when (e.HWStageTypeID = 2) and (a.enddate < g.startdate) then '1-Expired'
when (e.HWStageTypeID = 2) and (a.enddate <= g.enddate) then '2-Expiring'
when (e.HWStageTypeID = 2) then '4-Open'
when (e.HWStageTypeID = 3) and (a.closedate <= g.enddate)then '3-Ended'
when (e.HWStageTypeID = 3) and (a.closedate > g.enddate) then '4-Open'
when (e.HWStageTypeID = 3) and a.closedate IS NULL then '5-Ended But No Close Date'
else '?'
end),
d.clientname,
Recruiter = isnull((select top 1 x.fullname from users x
inner join OrderCandidateRecruiter y on y.userid = x.userid
where y.orderid = a.orderid and y.candidateid = a.candidateid
and y.entitytype in ('Primary','Secondary')
order by y.entitytype desc, ordercandidaterecruiter),'none entered'),
a.OrderID,
a.candidateid,
[SSN] = c.identificationdetails,
c.FullNameLFM,
startdate = isnull(convert(varchar(10),a.startingdate,101),'**********'),
enddate = case --switches close date to enddate
when e.HWStageTypeID = 3 then isnull(convert(varchar(10),a.closedate,101),'**********')
else isnull(convert(varchar(10),a.enddate,101),'**********')
end,
bill = isnull(a.bill,0),
pay = isnull(a.pay,0),
markup = case when isnull(a.pay,0) <> 0 then cast(isnull(a.bill,0)/a.pay as decimal(19,2)) else 0 end,
[OfficeDesc] = f.[description],
a.startingdate,
a.closedate,
HWStage = case
when e.HWStageTypeID = 3 then 'Ended'
else 'Placed'
end,
d.clientid, f.id, k.POnumber, j.DepartmentDesc, I.CostCenterCode
into #Results
from
vw_ordercandidates_all a (nolock)
inner join vw_orders b (nolock) on b.orderid = a.orderid
inner join clients d (nolock) on d.clientid = b.clientid
inner join clientbranch cb (nolock) on cb.branchid = b.branchid
inner join vw_candidates c on c.candidateid = a.candidateid
inner join stages e (nolock) on e.stageid = a.stageid
inner join offices f (nolock) on f.[id] = b.officeid
inner join @workperiods g on g.workscheduleid = b.workscheduleid
inner join vw_OrderCandidates h (nolock)
left outer join dbo.ClientCostCenters I (nolock)
on h.CostCenterID = I.CostCenterID
Left outer join dbo.ClientDepartments j (nolock)
on h.CustDeptID = j.CustDeptID
left outer Join dbo.ClientPurchaseOrders k (noLock)
on h.POID = k.PurchaseOrderID
on a.Orderid = h.Orderid and a.candidateid=h.candidateid
left outer join postaladdress p on p.entityid = cb.branchid and p.entitytypeid = 7
left outer join region n on n.regionid = p.regionid
where
b.tempordirect = 'T'
and
e.HWStageTypeID > 1
--and
--f.id = @OfficeID
and
isdate(a.placementdate) = 1
and
a.startingdate IS NOT NULL --placement must have a start date to qualify!
and
(
--Don't show up on the report if they're more than 90 days expired!
(e.HWStageTypeID = 2
AND
DATEDIFF(dd, a.enddate, g.startdate) < 90
)
OR
(e.HWStageTypeID = 3
AND
DATEDIFF(dd, a.closedate, g.startdate) < 90
)
)
and
( ((e.HWStageTypeID = 2)
and
(g.EndDate >= a.startingdate)) -- active placements must have started
or
((e.HWStageTypeID = 3)
and
(a.closedate >= g.startdate)) -- closed placements must not end before the period
or
((e.HWStageTypeID = 2)
and
(a.startingdate is null)) -- placements missing start dates
or
((e.HWStageTypeID = 3)
and
(a.closedate is null)) -- ended placements missing close dates
)
/*
and
g.EndDate <= isnull(a.closedate,a.enddate)
*/
order by
--1 desc,3,2,6
[recruiter],
[stage_desc],
d.[clientname]
--f.[id]
select * from #results where clientname like 'Macmillan%' and branchdescription like @branchdescription
drop table #results
June 7, 2010 at 7:40 pm
Recruiter = isnull((select top 1 x.fullname from users x
inner join OrderCandidateRecruiter y on y.userid = x.userid
where y.orderid = a.orderid and y.candidateid = a.candidateid
and y.entitytype in ('Primary','Secondary')
order by y.entitytype desc, ordercandidaterecruiter),'none entered')
You only have one subquery. This shouldn't return more than one result tho
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
June 7, 2010 at 8:04 pm
Thanks for the response, and that is where I started looking into the issue, and even if I comment that section out I still get the error? I have no clue where to look from there.
June 8, 2010 at 10:02 am
Check for the presence of a trigger on the table. My guess is that the trigger contains the subquery and that is only built to handle single row operations.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply