If statement in select (ORACLE)
438,732
Solution 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 --
Solution 2
So simple you can use case statement here.
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
Solution 3
In one line, answer is as below;
[ CASE WHEN COLUMN_NAME = 'VALUE' THEN 'SHOW_THIS' ELSE 'SHOW_OTHER' END as ALIAS ]
Author by
4est
Updated on July 09, 2022Comments
-
4est almost 2 years
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
if they are equal in new columns should be value 1 if are not equal should be 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
ORA-00920: invalid relational operator :(
-
Frank Schmitt about 11 yearsThis will return 0 if both values are null which might not be what the OP expects / wants (see my comment on the OP).
-
Admin over 7 yearsThis just saved my marriage. Thanks.
-
MadMad666 over 4 yearssometimes PL/SQL tries to destroy anyone marriage ! ha ha ha