SQL Problem with case statement

  • Can anyone tell me why the following SQL generates an error (from keyword not found where expected)

    SELECT Questions.QT_ANS_DUE_DATE, Answers.ANS_DATE,

    CASE WHEN Answers.ANS_DATE <= Questions.QT_ANS_DUE_DATE THEN 'Y' ELSE 'N' END AS AnswerOnTime,

    Questions.ROBIN_FLAG,

    RobinOnTime =

    CASE

    WHEN Questions.ROBIN_FLAG = 'Y' AND Answers.ANS_DATE <= Questions.QT_ANS_DUE_DATE AND Answers.ANS_DATE IS NOT NULL THEN 'Y'

    END

    FROM QUESTIONS Questions, QT_ANS Qt_ans, ANSWERS Answers, MEMBER Member

    WHERE (Questions.QT_ID = Qt_ans.QT_ID)

    AND (Qt_ans.ANS_ID = Answers.ANS_ID)

    AND (Questions.MEM_ID = Member.MEM_ID)

    AND ( (Questions.QT_PQ_CAT = 'N')

    AND (Member.MEM_TYPE = 'C') )

    ORDER BY QT_ANS_DUE_DATE Desc

    any help would be appreciated !

  • I believe you need an ELSE statement in the second CASE.

    Guarddata-

  • I can't see any problem which would prevent this statement from parsing. You don't have to supply an 'else' clause.

    Did you definitely copy and paste exactly the correct code?

    I guess the thing to do is to remove fields from the select list one by one and see which one fixes it.

    Do you have any more specific error info?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Should this line:

    CASE WHEN Answers.ANS_DATE <= Questions.QT_ANS_DUE_DATE THEN 'Y' ELSE 'N' END AS AnswerOnTime,

    be

    AnswerOnTime = CASE WHEN Answers.ANS_DATE <= Questions.QT_ANS_DUE_DATE THEN 'Y' ELSE 'N' END,

    Jeremy

  • Sorry - As I look again at the original SELECT, there is a RobinOnTime = CASE clause. If this is a variable, you probably need an @ symbol. If this is a column, it should be CASE... AS RobinOnTime

    Hope this helps

    Guarddata-

  • I would suggest using inner joins. Also, way use an alias if it is the same as the table name?

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • So, I checked the BOL... it seems like the ELSE clause is required, even if left empty...

    "ELSE else_result_expression

    Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion."

  • I agree with stax68--unless misuk11 provides more information, we'll never know. There is nothing inherently wrong with the query as written, so we cannot know what the problem is without more information like the schema, data, or, possibly, surounding code.

    It is fine to assign a column alias by "ColAlias = expression" or "expression AS ColAlias" or just "expression ColAlias".

    It is fine to have a CASE expression with no ELSE clause. As BOL states, the expression will evaluate to NULL if no WHEN predicate evaluates to True and there is no ELSE.

    I think Guarddata has a good point: RobinOnTime is the only apparent column name missing an alias; perhaps that is not a column but instead is meant to be a local variable, but is missing the @ prefix (and declaration in the code as given).

    --Jonathan



    --Jonathan

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

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