Error on Subquery

  • Please to resolve this issue.

    I have this query

    SELECT

    (select REB_TAX_RATE from PR_REB_TAX_RATE

    where EFF_DATE = (select MAX(EFF_DATE)

    from PR_REB_TAX_RATE

    group by EFF_DATE

    having EFF_DATE <= FR.REBATE_PAID_DATE))

    FROM PR_FUND_REBATES FR

    And I get error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    I tried few queries but every one lead to another error.

    Please help

  • Hi Hoseam,

    it seems as if the table PR_REB_TAX_RATE can have more than one entry for each EFF_DATE. And at least one of the selected rows in PR_FUND_REBATES has a REBATE_PAID_DATE that gives a EFF_DATE that has duplicates and thus you will get the error, try to put a COUNT in the subquery:

    SELECT (select COUNT(REB_TAX_RATE)

    from PR_REB_TAX_RATE

    where EFF_DATE = (select MAX(EFF_DATE)

    from PR_REB_TAX_RATE

    group by EFF_DATE

    having EFF_DATE <= FR.REBATE_PAID_DATE))

    , FR.*

    FROM PR_FUND_REBATES FR

    ORDER BY 1 DESC

    This way you will find the rows that gives the error so you can find a solution. 🙂

    /Markus

    hoseam (9/21/2012)


    Please to resolve this issue.

    I have this query

    SELECT

    (select REB_TAX_RATE from PR_REB_TAX_RATE

    where EFF_DATE = (select MAX(EFF_DATE)

    from PR_REB_TAX_RATE

    group by EFF_DATE

    having EFF_DATE <= FR.REBATE_PAID_DATE))

    FROM PR_FUND_REBATES FR

    And I get error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    I tried few queries but every one lead to another error.

    Please help

  • hoseam (9/21/2012)


    Please to resolve this issue.

    I have this query

    SELECT

    (select REB_TAX_RATE from PR_REB_TAX_RATE

    where EFF_DATE = (select MAX(EFF_DATE)

    from PR_REB_TAX_RATE

    group by EFF_DATE

    having EFF_DATE <= FR.REBATE_PAID_DATE))

    FROM PR_FUND_REBATES FR

    And I get error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    I tried few queries but every one lead to another error.

    Please help

    -- original with explanation

    SELECT

    FR.REBATE_PAID_DATE,

    REB_TAX_RATE = (

    SELECT REB_TAX_RATE

    FROM PR_REB_TAX_RATE

    WHERE EFF_DATE = (

    SELECT MAX(EFF_DATE)

    FROM PR_REB_TAX_RATE

    GROUP BY EFF_DATE -- the output will have one row per EFF_DATE

    HAVING EFF_DATE <= FR.REBATE_PAID_DATE

    )

    )

    FROM PR_FUND_REBATES FR

    -- original, fixed (unless there are dupes on EFF_DATE in PR_REB_TAX_RATE table)

    SELECT

    FR.REBATE_PAID_DATE,

    REB_TAX_RATE = (

    SELECT REB_TAX_RATE

    FROM PR_REB_TAX_RATE

    WHERE EFF_DATE = (

    SELECT MAX(EFF_DATE) -- one value returned

    FROM PR_REB_TAX_RATE

    WHERE EFF_DATE <= FR.REBATE_PAID_DATE

    )

    )

    FROM PR_FUND_REBATES FR

    -- more efficient

    SELECT

    FR.REBATE_PAID_DATE,

    x.REB_TAX_RATE

    FROM PR_FUND_REBATES FR

    CROSS APPLY (

    SELECT TOP 1 REB_TAX_RATE

    FROM PR_REB_TAX_RATE

    WHERE EFF_DATE <= FR.REBATE_PAID_DATE

    ORDER BY EFF_DATE DESC

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks Chris,

    I was too quick and missed the inner group by, which is the most likely to give the error...

    /Markus

  • Hunterwood (9/21/2012)


    Thanks Chris,

    I was too quick and missed the inner group by, which is the most likely to give the error...

    /Markus

    Hey Markus, no problem - it's always good to have an extra eye look things over and it works both ways - thanks for your confirmation.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • can u help me sir? i am new to db

    CREATE proc [dbo].[student_marks]

    @roll_no varchar(20)

    AS

    BEGIN

    CREATE TABLE #temp

    (

    semester INT,

    marks INT

    )

    INSERT INTO #temp

    (

    semester,

    marks

    )

    SELECT

    semester,

    ( SELECT SUM ( marks )/count(noof_sub)

    FROM student_details sd

    INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no

    WHERE sd.roll_no = @roll_no

    AND ri.semester = sm.sem_attended

    group by semester

    ) marks

    FROM student_details sd

    INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no

    WHERE sm.roll_no=@roll_no

    SELECT *FROM #temp2

    end

    the problem is when i execute the proc it returns 'subquery returned more than 1 value' error.because of that subquery contains 3 semesters and marks.so i just tried to remove that group by function in subquery then it returns same value for 3 rows.

    but i need the result like semester marks

    1 80

    2 75

    3 78

    what can i do to overcome this problem? thanks in advance....:-)

  • @nitha jen

    Please start a new thread for your issue. Hijacking other folks threads causes confusion. Thanks.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

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