Link to home
Start Free TrialLog in
Avatar of janthonyn
janthonyn

asked on

Help with CASE or DECODE sql syntax

I need help with a conditional expression that isn't working in a sql:

select rr_v.description
            (case when substr(rr_v.description,1,2)= 'PL' then'Income Statement'
                  when substr(rr_v.description,1,2)= 'BS' then 'Balance Sheet'    
                                   else 'N/A'
             end) rpt_type                      
    ,rr_v.name report
    ,rr_v.row_set
    ,rr_v.column_set
    ,nvl(rr_v.content_set,'None') content_set
    ,nvl(rr_v.report_display_set,'None') display_set
    ,nvl(rr_v.row_order,'None') row_order
    ,fu.description report_created_by
    ,trunc(rr_v.creation_date) crtd_dt
    ,trunc(rr_v.last_update_date) last_updt
from rg_reports_v rr_v
    ,fnd_user fu
where rr_v.created_by = fu.user_id
order by rr_v.name
####################################
Tried to use decode and got an error also with this:

select rr_v.name report
            ,decode(substr(rr_v.description,1,2)
                                 ,'PL','Income Statement'
                                 ,'BS' then 'Balance Sheet'    
                                 ,'N/A') rpt_type                      
    ,rr_v.row_set
    ,rr_v.column_set
    ,nvl(rr_v.content_set,'None') content_set
    ,nvl(rr_v.report_display_set,'None') display_set
    ,nvl(rr_v.row_order,'None') row_order
    ,fu.description report_created_by
    ,trunc(rr_v.creation_date) crtd_dt
    ,trunc(rr_v.last_update_date) last_updt
from rg_reports_v rr_v
    ,fnd_user fu
where rr_v.created_by = fu.user_id
order by rr_v.name

I'm trying to write a condition that takes a look at the first 2 characters in the description column of rg_reports_v and then returns report type name as specified in the condition. If I take the case and decode expressions out of these sqls, they run OK, but without the column I want for report type.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of janthonyn
janthonyn

ASKER

They both worked. Thanks!