September 21, 2012 at 12:11 am
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
September 21, 2012 at 12:59 am
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
September 21, 2012 at 2:18 am
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
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
September 21, 2012 at 2:46 am
Thanks Chris,
I was too quick and missed the inner group by, which is the most likely to give the error...
/Markus
September 21, 2012 at 3:00 am
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.
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
June 12, 2013 at 4:25 am
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....:-)
June 12, 2013 at 4:32 am
@nitha jen
Please start a new thread for your issue. Hijacking other folks threads causes confusion. Thanks.
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