subquery returned more then one value

Question

Post reply

subquery returned more then one value

krypto69

SSChampion

Points: 13604
More actions
August 8, 2012 at 7:41 am

#262444

I have this query that used to work just fine..now it's giving me a "Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I can't figure out whats' wrong?
SELECT
'WAS3' AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
(SELECT DATEDIFF(YEAR, EecDateOfOriginalHire, getdate()) from empcomp EC WHERE EC.EecEEID = E.EECEEID) as 'Yrs of Serv'
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
EecDateOfTermination IS NOT NULL
AND EXISTS
(SELECT 1
FROM EmpComp e2
WHERE e2.EecEEID = E.EecEEID
--AND e2.eecEmplStatus <> 'A')-- changed to <> ...this WAS/is to filter out anyone that was termed then re-hired
--AND E.EecTermReason NOT IN ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13', 'TRO')
AND E.eecDateOfTermination
IN (SELECT (EC.eecDateOfTermination)
FROM EMPCOMP EC
WHERE EC.EecEEID = E.EECEEID
AND EC.eecDateOfTermination IS NOT NULL
AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND E.eecDateOfTermination <= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))

Viewing 5 posts - 1 through 4 (of 4 total)

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

krypto69 SSChampion Points: 13604 More actions · Answer 1

I know now that my problem is in this part of the statement:

SELECT DATEDIFF(YEAR, EecDateOfOriginalHire, getdate()) from empcomp EC WHERE EC.EecEEID = eepeeid

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

I'd say it was this bit

SELECT DATEDIFF(YEAR, EecDateOfOriginalHire, getdate()) from empcomp EC WHERE EC.EecEEID = E.EECEEID) as 'Yrs of Serv'

which is returning more than one value for one or more E.EECEEIDs ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

This subquery might be returning more than one value.

(SELECT DATEDIFF(YEAR, EecDateOfOriginalHire, getdate()) from empcomp EC WHERE EC.EecEEID = E.EECEEID) as 'Yrs of Serv'

You can test it using this:

SELECT EC.EecEEID, COUNT(*)

FROM empcomp EC

GROUP BY EC.EecEEID

HAVING COUNT(*) > 1

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

krypto69 SSChampion Points: 13604 More actions · Answer 4

Yep that was it!

Thanks guys ...really really appreciated!