如果语句中的选择 (ORACLE)

标签: sql Oracle oracle10g
发布时间: 2017/4/15 18:27:30
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

嗨我有简单地选择和作品很大︰

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%'

现在我想要叫两个列︰ ISSUE_DIVISION 和 ISSUE_DIVISION_2

如果它们相等的新列应该是 1 如果是不相等的值应为 0,

如何能做到?


我完整的代码︰

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

但我行收到错误︰ Issue_Division_Result 作为其他 0 结束

ORA-00920︰ 无效的关系运算符:(

解决方法 1:

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 --
赞助商