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,'Non e') content_set
,nvl(rr_v.report_display_s et,'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_da te) 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.descri ption,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,'Non e') content_set
,nvl(rr_v.report_display_s et,'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_da te) 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.
select rr_v.description
(case when substr(rr_v.description,1,
when substr(rr_v.description,1,
else 'N/A'
end) rpt_type
,rr_v.name report
,rr_v.row_set
,rr_v.column_set
,nvl(rr_v.content_set,'Non
,nvl(rr_v.report_display_s
,nvl(rr_v.row_order,'None'
,fu.description report_created_by
,trunc(rr_v.creation_date)
,trunc(rr_v.last_update_da
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.descri
,'PL','Income Statement'
,'BS' then 'Balance Sheet'
,'N/A') rpt_type
,rr_v.row_set
,rr_v.column_set
,nvl(rr_v.content_set,'Non
,nvl(rr_v.report_display_s
,nvl(rr_v.row_order,'None'
,fu.description report_created_by
,trunc(rr_v.creation_date)
,trunc(rr_v.last_update_da
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER