英文:
multiple Case statement in a query
问题
这个语句出错的原因是因为 SQL 编辑器遇到了一些不符合语法规则的内容，导致无法正确解析查询。你可以检查以下部分，看看是否需要修复：
-
在这两行中，你使用了
'来表示单引号：not ACT.ActivityTime_Date like '2019-09-%'。SQL 中通常使用单引号'表示字符串，而不是'。你可以将这些地方修改为'，如：not ACT.ActivityTime_Date like '2019-09-%'。 -
在查询的最后，你使用了
from Analytics.ConnectTodayActive ACT;来指定表格。确保表格ConnectTodayActive存在并且已正确命名。 -
检查语法，确保查询的结构是正确的。如果仍然有问题，可能需要进一步检查整个查询以查找其他潜在的语法错误。
这些修复可能会解决你的问题。如果还有其他问题，请提供更多详细信息，以便更好地帮助你。
英文:
Can you please tell me why this statement is wrong, it is giving me an error that it was expecting something else instead of the case phrase
SELECT ACT.Employee_Id,
case when max( case when not ACT.ActivityTime_Date like '2019-09-%' and ACT.Activity_Time_Date not like '09/%/2019' and
not (ACT.Action like 'ACTIVITY_create_%' or ACT.Action like 'ACTIVITY_update_%') and
not ACT.Destination_ObjectType in ('announcement', 'attachment'
,'blog','blogpost','community', 'directmessage', 'document', 'event', 'idea', 'message', 'poll', 'project', 'question',
'socialgroup', 'task', 'thread', 'video', 'wallentry')) then 1 else 0
END)=0
then 'Contributor'
from Analytics.ConnectTodayActive ACT;
This is the error
SQL Editor 1: Encountered "case when max ( case when not ( ACT . ActivityTime_Date like \'2019-09-%\' or ACT . ActivityTime_Date like \'09/%/2019\' ) and not ( ACT . Action like not.
Was expecting one of:
"column" ...
"row" ...
"transactiontime" ...
"validtime" ...
"(" ...
"until_changed" ...
"until_closed" ...
<NAME> ...
"sampleid" ...
"interval" ...
"date" ...
"time" ...
"timestamp" ...
"begin" ...
"end" ...
"next" ...
<SINGLE_STRING_LITERAL> ...
<INTEGER_LITERAL> ...
<FLOATING_POINT_LITERAL> ...
":" ...
"?" ...
<NAMED_QUESTIONMARK> ...
"@" ...
"null" ...
"user" ...
"current_role" ...
"current_user" ...
"session" ...
"account" ...
"database" ...
"role" ...
"profile" ...
"zone" ...
"td_host" ...
"td_authid" ...
"new" ...
"current_date" ...
答案1
得分: 2
你的查询似乎格式不正确：
- 你外部的
CASE语句缺少一个END。 - 一个括号放置不当。
- 如果你想返回
employee_id，你需要使用GROUP BY，因为你使用了MAX。
你应该能够合并你的 LIKE 条件，所以类似这样：
SELECT
ACT.Employee_Id,
CASE
WHEN MAX (
CASE
WHEN
ACT.ActivityTime_Date NOT LIKE ALL('2019-09-%','09/%/2019') AND
ACT.Action NOT LIKE ALL ('ACTIVITY_create_%','ACTIVITY_update_%') AND
ACT.Destination_ObjectType NOT IN ('announcement','attachment','blog','blogpost','community', 'directmessage', 'document', 'event','idea', 'message', 'poll', 'project', 'question', 'socialgroup', 'task', 'thread', 'video', 'wallentry')
THEN 1
ELSE 0
END
) = 0 THEN 'Contributor'
END AS my_col
FROM Analytics.ConnectTodayActive ACT
GROUP BY ACT.Employee_Id;
英文:
Your query doesn't seem to be formatted correctly:
- your outer
CASEstatement is missing anEND - an end parenthesis is out of place
- if you want to return
employee_id, you need to use aGROUP BYsince you're usingMAX
You should be able to combine your LIKE conditions too, so something like this:
SELECT
ACT.Employee_Id,
CASE
WHEN MAX (
CASE
WHEN
ACT.ActivityTime_Date NOT LIKE ALL('2019-09-%','09/%/2019') AND
ACT.Action NOT LIKE ALL ('ACTIVITY_create_%','ACTIVITY_update_%') AND
ACT.Destination_ObjectType NOT IN ('announcement','attachment','blog','blogpost','community', 'directmessage', 'document', 'event','idea', 'message', 'poll', 'project', 'question', 'socialgroup', 'task', 'thread', 'video', 'wallentry')
THEN 1
ELSE 0
END
) = 0 THEN 'Contributor'
END AS my_col
FROM Analytics.ConnectTodayActive ACT
GROUP BY ACT.Employee_Id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论