October 1, 2003 at 7:19 am
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 !
October 1, 2003 at 8:16 am
I believe you need an ELSE statement in the second CASE.
Guarddata-
October 2, 2003 at 3:08 am
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
October 2, 2003 at 3:28 am
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
October 2, 2003 at 8:19 am
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-
October 2, 2003 at 9:40 am
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. 😉
October 2, 2003 at 1:52 pm
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."
October 2, 2003 at 3:31 pm
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