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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply