• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4883
  • Last Modified:

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.
0
janthonyn
Asked:
janthonyn
1 Solution
 
QlemoC++ DeveloperCommented:
select rr_v.description,
            (case substr(rr_v.description,1,2) when  'PL' then'Income Statement'
                  when  'BS' then 'Balance Sheet'    
                                   else 'N/A'
             end) rpt_type                      
    ,rr_v.name report

... or:

select rr_v.name report
            ,decode(substr(rr_v.description,1,2)
                                 ,'PL','Income Statement'
                                 ,'BS' , 'Balance Sheet'    
                                 ,'N/A') rpt_type                      
    ,rr_v.row_set
...
0
 
janthonynAuthor Commented:
They both worked. Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now