#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    6
    Rep Power
    0

    If statment in select


    Hi I have simply select and works great:

    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%'
    Now I want to callte 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 ?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by forestq
    how can I do it ?
    simple, just push your existing query down one level into a subquery...
    Code:
    SELECT *
         , CASE WHEN issue_division = issue_division_2
                THEN 1
                ELSE 0 END AS new_column
      FROM ( 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%' 
           ) AS q
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    6
    Rep Power
    0
    I get error:

    ORA-00923: FROM keyword not found where expected :/
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by forestq
    ORA-00923:
    dude... you realize you posted in the microsoft sql server forum, yes?

    i'll move your thread over to the oracle forum and those guys will help you out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388
    Originally Posted by forestq
    I get error:

    ORA-00923: FROM keyword not found where expected :/
    Try this:
    Code:
    SELECT q.*
         , CASE WHEN issue_division = issue_division_2
                THEN 1
                ELSE 0 END AS new_column
    . . .   E t c   . . .

IMN logo majestic logo threadwatch logo seochat tools logo