英文:
Oracle Apex - SQL Where with Case
问题
我有一个非常简单的查询，使用了组复选框：
P1_CHECKBOX_1
P1_CHECKBOX_2
还有一个非常简单的查询，用于根据这两个项目刷新一个基于交互式报告的查询，如下所示：
select
name,
last_name
from Emp_table
where
(case
when instr(:P1_CHECKBOX_2, name)>0
then instr(:P1_CHECKBOX_2, name)>0
else instr(:P1_CHECKBOX_1, last_name)>0
end) = 1
这个想法是使用复选框来筛选交互式报告。这两个项目都有独立的动态操作，用于刷新报告（受影响的元素是报告区域）。这里有什么我遗漏的吗？我收到这个错误：ORA-20999：解析SQL查询失败！ORA-06550：第161行，第8列：ORA-00905：缺少关键字。谢谢。
英文:
I have a really simple query using Group Checkboxes:
P1_CHECKBOX_1
P1_CHECKBOX_2
And a really simple query to refresh an Interactive Report based on those two items like this:
select
name,
last_name
from Emp_table
where
(case
when instr(:P1_CHECKBOX_2, name)>0
then instr(:P1_CHECKBOX_2, name)>0
else instr(:P1_CHECKBOX_1, last_name)>0
end) = 1
The idea is to filter the IG using the checkboxes.
Both items have an independent dynamic action that refreshes the report (affected elements the report region).
Is there anything I'm missing here?
I'm getting this error:
ORA-20999: Failed to parse SQL query! ORA-06550: line 161, column 8: ORA-00905: missing
keyword
Thanks
答案1
得分: 2
The THEN and ELSE clauses should return values and not contain comparisons:
select name,
last_name
from Emp_table
where case
when instr(:P1_CHECKBOX_2, name)>0
then instr(:P1_CHECKBOX_2, name)
else instr(:P1_CHECKBOX_1, last_name)
end > 0
或者更简单地，不使用 CASE 表达式：
select name,
last_name
from Emp_table
where instr(:P1_CHECKBOX_2, name)>0
or instr(:P1_CHECKBOX_1, last_name) > 0
英文:
The THEN and ELSE clauses should return values and not contain comparisons:
select name,
last_name
from Emp_table
where case
when instr(:P1_CHECKBOX_2, name)>0
then instr(:P1_CHECKBOX_2, name)
else instr(:P1_CHECKBOX_1, last_name)
end > 0
or, more simply, without the CASE expression:
select name,
last_name
from Emp_table
where instr(:P1_CHECKBOX_2, name)>0
or instr(:P1_CHECKBOX_1, last_name) > 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论