SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SubQuery returning more one record


SubQuery returning more one record

Author
Message
alorenzini
alorenzini
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 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!
antonio.collins
antonio.collins
SSC Eights!
SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)

Group: General Forum Members
Points: 854 Visits: 921

, (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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90049 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


alorenzini
alorenzini
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90049 Visits: 45284
I'll have a go at it later this evening, if someone else doesn't reply first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


alorenzini
alorenzini
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 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!
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4189 Visits: 3433
Replace DISTINCT with TOP 1 in the correlated subquery.


N 56°04'39.16"
E 12°55'05.25"
antonio.collins
antonio.collins
SSC Eights!
SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)

Group: General Forum Members
Points: 854 Visits: 921

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.
alorenzini
alorenzini
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90049 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search