Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SubQuery returning more one record Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 4:02 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 11, 2008 7:41 AM
Points: 340, Visits: 476
I have the following code:
-- Declare the Downline Recursive Query
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,NACDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID)
AS
(

-- Anchor member defintion
SELECT A.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,CAST(A.SponsorID AS Nvarchar(MAX))
,'' As SponsorName
,0 as DownLineLevel
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
FROM dbo.uvwConsultantDownLine A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate
--AND A.StatusID NOT IN ('Inactive') -- 1 Record
UNION ALL
--Recursive member definition
SELECT A.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID
,'' AS SponsorName
,DownLineLevel + 1
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID

FROM dbo.uvwConsultantDownLine AS A with (nolock)
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
WHERE @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate
--AND A.StatusID NOT IN ('Inactive')
) -- Appropriate records

-- Create the Temp table #Downline that returns the CTE results
SELECT A.DownLineLevel
,A.ConsultantID
,A.EffectiveDate
,UPPER(RTRIM(A.ConsultantName)) AS ConsultantName
-- ,C.EmailAddress
,D.Title AS AchievedTitle
, CONVERT(NVARCHAR(MAX),A.SponsorID) AS SponsorID
,A.SponsorName
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
INTO #Downline
FROM DownLine AS A with (noLock)
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON A.CurrentLevelXID = D.XID
WHERE DownLineLevel <=4
GROUP BY A.ConsultantID
,A.EffectiveDate
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
-- ,C.BumpUpDate
-- ,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID

Select
D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.EffectiveDate
,(SELECT DISTINCT b1.FirstName + ' ' + b1.LastName FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1 ON D.SponsorID = B1.ConsultantID) AS SponsorName

,D.SponsorID
,D.AchievedTitle
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS QuarterToDate_total
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS MonthToDate_Total
,D.ConsultantXID
,D.SponsorXID
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt),0) AS QuarterToDate_Volume
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @MonthStartDt And @MonthEndDt),0) AS MonthToDate_Volume
,D.Active
,ISNULL(r.RepFlag,' ') AS RepFlag
,'StatusID' = CASE
WHEN StatusID ='Active' THEN ''
WHEN StatusID = 'Home Office' THEN ''
WHEN StatusID = 'New, Non Active Consultant' THEN ''
ELSE StatusID
END
,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,'ShowFlag' = CASE
WHEN @PeriodDate Between @CurrMonthStartDt AND @CurrMonthEndDt THEN 'X'
ELSE ' '
END
INTO #DLLevel from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 4
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelAchieveDate
,D.BumpupDate
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,r.RepFlag
,D.StatusID


SELECT * FROM #DLLevel

DROP TABLE #Downline
DROP TABLE #DLLevel


The bolded code is suppose to bring back one name, the sponsor name. I have tried to do a distinct on it but it keeps returning the following error:

Msg 512, Level 16, State 1, Line 126
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
The statement has been terminated.

Any advice would be appreciated.


Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Post #449736
Posted Wednesday, January 30, 2008 4:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909

, (SELECT DISTINCT b1.FirstName + ' ' + b1.LastName
FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1
ON D.SponsorID = B1.ConsultantID) AS SponsorName


the left outer join is the problem. it's going to return all the distinct records dbo.consultant regardless of any corresponding SponsorId
Post #449742
Posted Wednesday, January 30, 2008 11:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 40,177, Visits: 36,579
Subqueries in the select clause (corrolated sub queries) have a tendency to perform very, very badly. Unless the optimiser is really smart (generally only on simpler queries) the subquery can get run once for each row in the outer select. It's a potential cursor in disguise.

I would suggest you try and move the sub queries into the from clause as derived tables. You won't get the qubqery error, and you may get better performance.

SELECT ...
FROM SomeTable INNER JOIN (SELECT Columns FROM SomeOtherTable) as ADerivedTable ON ....



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #449819
Posted Thursday, January 31, 2008 6:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 11, 2008 7:41 AM
Points: 340, Visits: 476
I am at a bit of a loss here. I think I understand what you are saying but not sure how to go about writing it.



Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Post #449896
Posted Thursday, January 31, 2008 6:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 40,177, Visits: 36,579
I'll have a go at it later this evening, if someone else doesn't reply first.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #449904
Posted Thursday, January 31, 2008 6:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 11, 2008 7:41 AM
Points: 340, Visits: 476
So how would I fix it?
ConsultantID and the SponsorID are in the same record.


Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Post #449917
Posted Thursday, January 31, 2008 6:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:11 AM
Points: 2,397, Visits: 3,416
Replace DISTINCT with TOP 1 in the correlated subquery.



N 56°04'39.16"
E 12°55'05.25"
Post #449920
Posted Thursday, January 31, 2008 7:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909

Select ...
,(SELECT DISTINCT b1.FirstName + ' ' + b1.LastName FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1 ON D.SponsorID = B1.ConsultantID) AS SponsorName
,D.SponsorID
...
from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'


in your query, consultant and uvwConsultantDownLine have no join criteria between themselves. the criteria is between uvwConsultantDownLine and #Dowline.

Post #449926
Posted Thursday, January 31, 2008 7:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 11, 2008 7:41 AM
Points: 340, Visits: 476
That worked thanks. But I would like to here more about how to do it faster. Maybe with the derived table that was mentioned.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Post #449927
Posted Friday, February 1, 2008 1:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 40,177, Visits: 36,579
Give this a try. Plese note, it is untested. I don't have your tables or data. It passes a syntax check, but that's all I could do.

Changes are in red.


Select D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.EffectiveDate
,B1.FirstName + ' ' + B1.LastName AS SponsorName
,D.SponsorID
,D.AchievedTitle
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,ISNULL(Sum(CASE
WHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS QuarterToDate_total
,ISNULL(Sum(CASE
WHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS MonthToDate_Total
,D.ConsultantXID
,D.SponsorXID
,ISNULL(QuarterVolume,0) AS QuarterToDate_Volume
,ISNULL(MonthVolume,0) AS MonthToDate_Volume

,D.Active
,ISNULL(r.RepFlag,' ') AS RepFlag
,CASE StatusID
WHEN 'Active' THEN ''
WHEN 'Home Office' THEN ''
WHEN 'New, Non Active Consultant' THEN ''
ELSE StatusID
END AS StatusID

,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,CASE WHEN @PeriodDate Between @CurrMonthStartDt AND @CurrMonthEndDt THEN 'X'
ELSE ' '
END AS ShowFlag

INTO #DLLevel

-- not much point in nolock on a temp table. Hint only applies to a single table
FROM #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT OUTER JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'

-- Was there a reason for the uvwConsultantDownLine table?
-- Took it out of the subquery, because it didn't appear to be necessary.
-- Hence subquery reduced to one table and joined in.
LEFT OUTER JOIN dbo.consultant AS B1 ON D.SponsorID = B1.ConsultantID
-- Added from select clause. Merged 2 subqueries into 1, since 1 was a subset of the other
LEFT OUTER JOIN (SELECT ConsultantID,
SUM(v.PartOneTotal) AS QuarterVolume,
SUM (CASE WHEN V.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN PartOneTotal ELSE 0 END) AS MonthVolume
FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON V.OrderTypeXID = T.XID
WHERE (T.OrderType != 'Credit') AND V.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt
GROUP BY ConsultantID) VT ON D.ConsultantID = VT.ConsultantID

-- Missing brackets here. AND has precedence over OR.
WHERE ((D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt))
AND r.AchieveLevel >= 4

-- Might need some modification
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelAchieveDate
,D.BumpupDate
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,r.RepFlag
,D.StatusID






Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #450344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse