Sub Query returning multiple rows for particular inputs

Question

Post reply

Sub Query returning multiple rows for particular inputs

saurabh.kumar

SSC Enthusiast

Points: 151
More actions
January 16, 2010 at 9:54 pm

#215638

The below SQL query is giving error for few employee ids in my db,I am unable to find where the problem is likely to be present.Please help.

select -- a.vcrFrom + a.vcrTo as 'Review Period',

a.vcrYear,c.vcrPhase,c.guidtransaction,a.guidCycleNo,c.vcremployeeid,(b.vcrFirstName + ' ' + b.vcrLastName ) as vcrName,

isnull((select vcrstatusdescription from tblstatusmaster d

where --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

c.vcrManagerSignOff = d.vcrManagerSignOff and

c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

c.vcrHRFeed = d.vcrCommHRFeed and

c.vcrSendToTM = d.vcrCommSendToTM and

d.vcrpriority is not null),'Goal Settings Not Initiated') as 'StatusCompleted',

isnull((select vcrNextAction from tblstatusmaster d

where --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

c.vcrManagerSignOff = d.vcrManagerSignOff and

c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

c.vcrHRFeed = d.vcrCommHRFeed and

c.vcrSendToTM = d.vcrCommSendToTM and

d.vcrpriority is not null),'-') as 'NextAction',

isnull((select vcrPendingWith from tblstatusmaster d

where --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

c.vcrManagerSignOff = d.vcrManagerSignOff and

c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

c.vcrHRFeed = d.vcrCommHRFeed and

c.vcrSendToTM = d.vcrCommSendToTM and

d.vcrpriority is not null),'Team Member') as 'PendingWith',

isnull((select vcrStatus from tblstatusmaster d

where --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

c.vcrManagerSignOff = d.vcrManagerSignOff and

c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

c.vcrHRFeed = d.vcrCommHRFeed and

c.vcrSendToTM = d.vcrCommSendToTM and

d.vcrpriority is not null),'-') as 'StatusCode' ,

isnull((select vcrRedirectPage from tblstatusmaster d

where --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

c.vcrManagerSignOff = d.vcrManagerSignOff and

c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

c.vcrHRFeed = d.vcrCommHRFeed and

c.vcrSendToTM = d.vcrCommSendToTM and

d.vcrpriority is not null),'-') as 'RedirectPage' ,

isnull((select vcrActivity from tblstatusmaster d

where --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

c.vcrManagerSignOff = d.vcrManagerSignOff and

c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

c.vcrHRFeed = d.vcrCommHRFeed and

c.vcrSendToTM = d.vcrCommSendToTM and

d.vcrpriority is not null),'-') as 'Activity'

-- b.dteDuedate as 'Due Date' from tblusertogoalmapping c

-- join tblcycle e on c.guidcycleno = e.guidcycleno

-- join tblphase a on a.vcrYear = e.vcryear and c.vcrphase = a.vcrphase

-- join tblduedates b on b.vcrcycle = e.guidcycleno and b.vcrphase = a.vcrphase

from tblusertogoalmapping c

join tblcycle a on a.guidcycleNo = c.guidcycleNo

join vUser b on b.vcrEmployeeID = c.vcremployeeid

where c.vcremployeeid in (select vcremployeeid from vmanager where vcrmanagerid ='745991'

and a.vcrYear = '2009' )

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

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Please post table scripts, index definitions, and sample data. Also, please provide a sample of the output you are seeing, and the desired output.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

It means that one of the queries inside the isnull function returns more then 1 value, which is not allows. Here is a small demonstrations of the subject:



--returns an error

select isnull((select 1 union select 2 union select null),0)

declare @tbl table (i int)

insert into @tbl (i)

select 1 union select 2 union select null

--Should work because each time that the function isnull is envoked

--it gets one value from i

select isnull(i,-1) from @tbl

--Should get an error, because the select i inside the

--isnull function produces more then 1 value

select isnull((select i from @tbl),-1) from @tbl

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Those six correlated subqueries all point (correlate) to the same row in table tblstatusmaster, so why not change the query so that tblstatusmaster is LEFT JOINed in the FROM list instead, like this:

select -- a.vcrFrom + a.vcrTo as 'Review Period',



	a.vcrYear, c.vcrPhase, c.guidtransaction, a.guidCycleNo, c.vcremployeeid, (b.vcrFirstName + ' ' + b.vcrLastName ) as vcrName,

	isnull(d.vcrstatusdescription,'Goal Settings Not Initiated') as 'StatusCompleted',

	isnull(d.vcrNextAction,'-') as 'NextAction',

	isnull(d.vcrPendingWith,'Team Member') as 'PendingWith',

	isnull(d.vcrStatus,'-') as 'StatusCode',

	isnull(d.vcrRedirectPage,'-') as 'RedirectPage',

	isnull(d.vcrActivity,'-') as 'Activity' 

-- b.dteDuedate as 'Due Date' from tblusertogoalmapping c

-- join tblcycle e on c.guidcycleno = e.guidcycleno

-- join tblphase a on a.vcrYear = e.vcryear and c.vcrphase = a.vcrphase

-- join tblduedates b on b.vcrcycle = e.guidcycleno and b.vcrphase = a.vcrphase 

from tblusertogoalmapping c

join tblcycle a on a.guidcycleNo = c.guidcycleNo

join vUser b on b.vcrEmployeeID = c.vcremployeeid 

LEFT JOIN (

	SELECT vcrstatusdescription, vcrNextAction, vcrPendingWith, vcrStatus, vcrRedirectPage, vcrActivity,

		vcrEmployeeSignOff,

		vcrManagerSignOff,

		vcrSendForReviewSelfAssessment,

		vcrManagerReviewSignOff,

		vcrSelfReviewSignOff,

		vcrComnMgrSignOff,

		vcrCommEmployeeSignOff,

		vcrCommSkipMgrSignOff,

		vcrCommHRFeed,

		vcrCommSendToTM

	FROM tblstatusmaster

	WHERE vcrpriority is not null) d

ON --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

	c.vcrEmployeeSignOff = d.vcrEmployeeSignOff and

	c.vcrManagerSignOff = d.vcrManagerSignOff and

	c.vcrSendForReviewSelfAssessment = d.vcrSendForReviewSelfAssessment and

	c.vcrManagerReviewSignOff = d.vcrManagerReviewSignOff and

	c.vcrSelfReviewSignOff = d.vcrSelfReviewSignOff and

	c.vcrComnMgrSignOff = d.vcrComnMgrSignOff and

	c.vcrCommEmployeeSignOff = d.vcrCommEmployeeSignOff and

	c.vcrCommSkipMgrSignOff = d.vcrCommSkipMgrSignOff and

	c.vcrHRFeed = d.vcrCommHRFeed and

	c.vcrSendToTM = d.vcrCommSendToTM

WHERE c.vcremployeeid in (select vcremployeeid from vmanager where vcrmanagerid ='745991'

and a.vcrYear = '2009' )

Those correlated subqueries error out when they return more than one row. The derived table (a SELECT ...FROM... in your FROM list) does not, it will result in multiple rows in your output instead. This allows you to eyeball the values from tblstatusmaster to decide which ones to keep.

Now here's a handy little trick: spool the output to a local temporary table, say #statusmaster, and you can examine those rows where there would be more than one row returned by the correlated subqueries, in isolation from those which don't:

SELECT s.*, d.d_Rows

FROM #statusmaster s

INNER JOIN ( -- pick up all duped rows

SELECT

vcrYear, vcrPhase, guidtransaction, guidCycleNo, vcremployeeid, vcrName, COUNT(*) AS d_Rows,

StatusCompleted,

NextAction,

PendingWith,

StatusCode,

RedirectPage,

Activity

FROM #statusmaster

GROUP BY vcrYear, vcrPhase, guidtransaction, guidCycleNo, vcremployeeid, vcrName

HAVING COUNT(*) > 1

) d -- /pick up all duped rows

ON d.vcrYear = s.vcrYear

AND d.vcrPhase = s.vcrPhase

AND d.guidtransaction = s.guidtransaction

AND d.guidCycleNo = s.guidCycleNo

AND d.vcremployeeid = s.vcremployeeid

AND d.vcrName = s.vcrName

ORDER BY s.vcrYear, s.vcrPhase, s.guidtransaction, s.guidCycleNo, s.vcremployeeid, s.vcrName

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

OomBoom Hall of Fame Points: 3481 More actions · Answer 4

You can use the following to check where you duplicate records is.

select d.vcrEmployeeSignOff,

d.vcrManagerSignOff,

d.vcrSendForReviewSelfAssessment,

d.vcrManagerReviewSignOff,

d.vcrSelfReviewSignOff,

d.vcrComnMgrSignOff,

d.vcrCommEmployeeSignOff,

d.vcrCommSkipMgrSignOff,

d.vcrCommHRFeed,

d.vcrCommSendToTM,

d.vcrpriority,COUNT(*) from tblstatusmaster d

group by --c.vcrSendForReviewGoals = d.vcrSendForReviewGoals and

d.vcrEmployeeSignOff,