Menu
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 :(
445
In Oracle, the IF-THEN-ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.
The condition is a Boolean variable, constant, or expression that evaluates to TRUE, FALSE, or NULL. If condition evaluates to TRUE, the executable statements found after the THEN keyword and before the matching END IF statement are executed. If condition evaluates to FALSE or NULL, those statements are not executed.
A SELECT statement consists of a query with an optional ORDER BY clause, an optional result offset clause, an optional fetch first clause, an optional FOR UPDATE clause and optionally isolation level. The SELECT statement is so named because the typical first word of the query construct is SELECT.
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 -- 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
