Post reply

Cannot check vales returned from a function in a CASE statement

  • July 30, 2008 at 7:34 am

    #188818

    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

    #850537

    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

    “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

  • July 30, 2008 at 11:56 pm

    #850996

    Thanks for pointing out the mistake

  • August 1, 2008 at 3:02 am

    #851702

    You're welcome, and thanks for the feedback.

    “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 4 posts - 1 through 3 (of 3 total)

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