January 30, 2008 at 4:02 pm
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
SELECTA.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
SELECTA.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!
January 30, 2008 at 4:18 pm
, (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
January 30, 2008 at 11:28 pm
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
January 31, 2008 at 6:26 am
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!
January 31, 2008 at 6:36 am
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
January 31, 2008 at 6:48 am
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!
January 31, 2008 at 6:52 am
Replace DISTINCT with TOP 1 in the correlated subquery.
N 56°04'39.16"
E 12°55'05.25"
January 31, 2008 at 7:05 am
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.
January 31, 2008 at 7:06 am
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!
February 1, 2008 at 1:11 am
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 ONV.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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply