Click here to monitor SSC
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
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
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!
antonio.collins
antonio.collins
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47145 Visits: 44346
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
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
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!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47145 Visits: 44346
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
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
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!
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

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


N 56°04'39.16"
E 12°55'05.25"
antonio.collins
antonio.collins
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 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
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
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!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47145 Visits: 44346
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