英文:
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' andnot (ACT.Action like 'ACTIVITY_create_%' or ACT.Action like 'ACTIVITY_update_%') andnot 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 0END)=0then '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 条件，所以类似这样：
SELECTACT.Employee_Id,CASEWHEN MAX (CASEWHENACT.ActivityTime_Date NOT LIKE ALL('2019-09-%','09/%/2019') ANDACT.Action NOT LIKE ALL ('ACTIVITY_create_%','ACTIVITY_update_%') ANDACT.Destination_ObjectType NOT IN ('announcement','attachment','blog','blogpost','community', 'directmessage', 'document', 'event','idea', 'message', 'poll', 'project', 'question', 'socialgroup', 'task', 'thread', 'video', 'wallentry')THEN 1ELSE 0END) = 0 THEN 'Contributor'END AS my_colFROM Analytics.ConnectTodayActive ACTGROUP 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:
SELECTACT.Employee_Id,CASEWHEN MAX (CASEWHENACT.ActivityTime_Date NOT LIKE ALL('2019-09-%','09/%/2019') ANDACT.Action NOT LIKE ALL ('ACTIVITY_create_%','ACTIVITY_update_%') ANDACT.Destination_ObjectType NOT IN ('announcement','attachment','blog','blogpost','community', 'directmessage', 'document', 'event','idea', 'message', 'poll', 'project', 'question', 'socialgroup', 'task', 'thread', 'video', 'wallentry')THEN 1ELSE 0END) = 0 THEN 'Contributor'END AS my_colFROM Analytics.ConnectTodayActive ACTGROUP BY ACT.Employee_Id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论