July 30, 2008 at 7:34 am
quite a simple query i just want to check the scores before inserting them
INSERT INTO Tablex(a,b,c,d,ScoreA,ScoreB )
SELECTDISTINCT a,b,c,d,
(CASE WHEN ((A.CompType='C' AND A.CustomerNA ='Y')) THEN (SELECT ScoreA FROM GetScoreA(A.a,A.b,A.c,A.ProductId,A.d))
WHEN ((A.CompType='B' AND A.BusinessNA ='Y'))
THEN (SELECT ScoreA FROM GetScoreA(A.a,A.b,A.c,A.ProductId,A.d))
ELSE (SELECT ScoreA FROM GetScoreA(A.a,A.b,A.c,A.ProductId,A.d))
END),
(CASE WHEN ((A.CompType='C' AND A.CustomerNA ='Y')) THEN (SELECT ScoreB FROM GetScoreB(A.a,A.b,A.c,A.ProductId,A.d))
WHEN ((A.CompType='B' AND A.BusinessNA ='Y')) THEN (SELECT ScoreB FROMGetScoreB(A.a,A.b,A.c,A.ProductId,A.d))
ELSE (SELECT ScoreB FROM GetScoreB(A.a,A.b,A.c,A.ProductId,A.d))
END)
FROM A
WHERE ScoreA = A.PointsAvailable --These statements
AND ScoreB = A.PointsReceived --are giving an error
July 30, 2008 at 8:18 am
ScoreA and ScoreB don't exist for the WHERE to use yet.
INSERT INTO Tablex (a,b,c,d,ScoreA,ScoreB)
SELECT a,b,c,d,ScoreA,ScoreB
FROM (
SELECT DISTINCT a,b,c,d,
ScoreA = (CASE WHEN ((A.CompType='C' AND A.CustomerNA ='Y')) THEN (SELECT ScoreA FROM GetScoreA(A.a,A.b,A.c,A.ProductId,A.d))
WHEN ((A.CompType='B' AND A.BusinessNA ='Y')) THEN (SELECT ScoreA FROM GetScoreA(A.a,A.b,A.c,A.ProductId,A.d))
ELSE (SELECT ScoreA FROM GetScoreA(A.a,A.b,A.c,A.ProductId,A.d))
END),
ScoreB = (CASE WHEN ((A.CompType='C' AND A.CustomerNA ='Y')) THEN (SELECT ScoreB FROM GetScoreB(A.a,A.b,A.c,A.ProductId,A.d))
WHEN ((A.CompType='B' AND A.BusinessNA ='Y')) THEN (SELECT ScoreB FROM GetScoreB(A.a,A.b,A.c,A.ProductId,A.d))
ELSE (SELECT ScoreB FROM GetScoreB(A.a,A.b,A.c,A.ProductId,A.d))
END),
A.PointsAvailable, A.PointsReceived
FROM A
) d
WHERE d.ScoreA = d.PointsAvailable --These statements
AND d.ScoreB = d.PointsReceived --are giving an error
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
July 30, 2008 at 11:56 pm
Thanks for pointing out the mistake
August 1, 2008 at 3:02 am
You're welcome, and thanks for the feedback.
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy