SubQuery returning more one record

  • 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!

  • , (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

  • 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
  • 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!

  • 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
  • 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!

  • Replace DISTINCT with TOP 1 in the correlated subquery.


    N 56°04'39.16"
    E 12°55'05.25"

  • 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.

  • 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!

  • 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

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

Viewing 10 posts - 1 through 9 (of 9 total)

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