If statement in select ORACLE

Question

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

User · Answer

In one line  answer is as below     CASE WHEN COLUMN NAME    VALUE  THEN  SHOW THIS  ELSE  SHOW OTHER  END as ALIAS

User · Answer

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

User · Answer

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

User · Answer

SELECT  CASE WHEN ISSUE DIVISION   ISSUE DIVISION 2 THEN 1 ELSE 0 END  AS ISSUES     --   lt add any columns to outer select from inner query gt     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 --

[sql] If statement in select (ORACLE)

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to if-statement

Examples related to oracle10g