SQL 选择中的 if 语句 (ORACLE)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15614751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If statement in select (ORACLE)
提问by 4est
Hi I have simply select and works great:
嗨，我只是选择并且效果很好：
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,
gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i
inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to call two columns: ISSUE_DIVISION and ISSUE_DIVISION_2
现在我想调用两列：ISSUE_DIVISION 和 ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,
如果它们在新列中相等应该是值 1 如果不相等应该是 0，
how can I do it ?
我该怎么做 ？
my full code:
我的完整代码：
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,
gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i
inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%' and
CASE WHEN ISSUE_DIVISION = ISSUE_DIVISION_2 THEN
CASE WHEN ISSUE_DIVISION is null then "Null Value found"
Else 1 End
ELSE 0 END As Issue_Division_Result
but I get error on line: ELSE 0 END As Issue_Division_Result
但我在线收到错误：ELSE 0 END As Issue_Division_Result
ORA-00920: invalid relational operator :(
ORA-00920: 无效的关系运算符 :(
回答by Art
SELECT (CASE WHEN ISSUE_DIVISION = ISSUE_DIVISION_2 THEN 1 ELSE 0 END) AS ISSUES
-- <add any columns to outer select from inner query>
FROM
( -- your query here --
select 'CARAT Issue Open' issue_comment, ...., ...,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from ....
where UPPER(ISSUE_STATUS) like '%OPEN%'
)
WHERE... -- optional --
回答by Dhinakar
So simple you can use casestatement here.
如此简单，您可以在这里使用case语句。
CASE WHEN ISSUE_DIVISION = ISSUE_DIVISION_2 THEN
CASE WHEN ISSUE_DIVISION is null then "Null Value found" //give your option
Else 1 End
ELSE 0 END As Issue_Division_Result
回答by Nisar
use the variable, Oracle does not support SQL in that context without an INTO. With a properly named variable your code will be more legible anyway.
使用该变量，Oracle 不支持没有 INTO 的上下文中的 SQL。无论如何，使用正确命名的变量，您的代码将更加清晰。
回答by JR Sahoo.'JS'
In one line, answer is as below;
在一行中，答案如下；
[ CASE WHEN COLUMN_NAME = 'VALUE' THEN 'SHOW_THIS' ELSE 'SHOW_OTHER' END as ALIAS ]
[ CASE WHEN COLUMN_NAME = 'VALUE' THEN 'SHOW_THIS' ELSE 'SHOW_OTHER' END 作为 ALIAS ]
