-
Can we decode a already decoded statement?
I have column which displays zeros by using NVL function for some rows . I want to display the same value for all rows for that column.
this is the query
create or replace view vw_post_to_treasury as
(SELECT distinct
ltrim(rtrim(substr(tc_line_rev_acct_no,1,5),'-'),'-')Fund,
ltrim(rtrim(substr(tc_line_rev_Acct_no,7,4),'-'),'-')Org,
ltrim(rtrim(substr(tc_line_rev_acct_no,12,5),'-'),'-')Account,
ltrim(rtrim(substr(tc_line_rev_acct_no,17,3),'-'),'-')Program,
to_date(v.tc_tran_date,'dd-MON-yy') Payin_date,
(v.TOTAMTPD-(sum(nvl(p.pw_amount,0)))) Amount,
decode(p.payin_prefix,' ',28,0,28,null,28,p.payin_prefix) Payin_prefix,
--nvl(decode(p.pw_payin_no,' ',p.pw_payin_no,0,pw_payin_no,null,p.pw_payin_no,p.pw_payin_no),0) Payin_No,
nvl(p.pw_payin_no,0) Payin_no, *****here is the problem.
decode(p.pw_cr_deb_ind,' ','D',null,'D','D')Cr_deb_ind,
decode(p.post_flag,' ','N',null,'N',p.post_flag)Post_flag,
'one stop payins '||28||'-'||nvl(p.pw_payin_no,0)comments --decode(p.pw_payin_no,' ',p.pw_payin_no,null,p.pw_payin_no,0,p.pw_payin_no,p.pw_payin_no) comments
from vw_payin_line v, payin_withdrawal p
where v.tc_tran_date= p.pw_payin_date(+)
and v.tc_line_rev_acct_no= p.pw_rev_acct_code(+)
group by v.tc_line_rev_acct_no,v.tc_tran_date,v.totamtpd,
p.payin_prefix,p.pw_payin_no,p.pw_cr_deb_ind,p.post_flag )
-
Yes we ca do that:
SQL> select decode(
decode(
username,'SYS','ORADBA','SYSTEM','ORADBA','USERS'),'USERS','SOME USER', 'GRAIT GAY') from all_users;
DECODE(DE
---------
GRAIT GAY
GRAIT GAY
SOME USER
SOME USER
SOME USER
SOME USER
SOME USER
SOME USER
-
Thanks Shestakov. In that example you gave (in the inner decode function )can we use a field_name instead of users.Users is just a string you gave but I want to display a column value there instead some other value.
-
u can just little bit modify this example:
in this example i used :
-- internal decode and
-- choice from different dields in decode
I hope this is enough.
select object_name,
decode(object_type,
'TABLE', 'This is table of schema :'|| decode(owner,'SYS','SYS DBA',owner),
'INDEX', 'This index created : '||to_char(created),
object_type)
from all_objects
;
AQ$_MESSAGE_TYPES This is table of schema :SYS DBA
AQ$_MSGTYPES_PRIMARY This index created : 16-APR-01
AQ$_NOTIFY_MSG TYPE
AQ$_PENDING_MESSAGES This is table of schema :SYS DBA
AQ$_PROPAGATION_SEQUENCE SEQUENCE
AQ$_PROPAGATION_STATUS This is table of schema :SYS DBA
AQ$_PROPAGATION_STATUS_PRIMARY This index created : 16-APR-01
AQ$_QTABLE_AFFINITIES_PK This index created : 16-APR-01
AQ$_QUEUE_STATISTICS This is table of schema :SYS DBA
AQ$_QUEUE_STATITICS_PK This index created : 16-APR-01
AQ$_QUEUE_TABLE_AFFINITIES This is table of schema :SYS DBA
AQ$_RECIPIENTS TYPE
AQ$_SCHEDULES This is table of schema :SYS DBA
AQ$_SCHEDULES_PRIMARY This index created : 16-APR-01
AQ$_SUBSCRIBERS TYPE
ARGUMENT$ This is table of schema :SYS DBA
ASSOC1 This index created : 16-APR-01
ASSOC2 This index created : 16-APR-01
ASSOCIATION$ This is table of schema :SYS DBA
ATEMPIND$ This index created : 16-APR-01
ATEMPTAB$ This is table of schema :SYS DBA
ATTRCOL$ This is table of schema :SYS DBA
AQ$_QUEUES_CHECK This index created : 16-APR-01
AQ$_QUEUES_PRIMARY This index created : 16-APR-01
AQ$_QUEUE_TABLES This is table of schema :SYSTEM
AQ$_QUEUE_TABLES_PRIMARY This index created : 16-APR-01
AQ$_SCHEDULES This is table of schema :SYSTEM
AQ$_SCHEDULES_CHECK This index created : 16-APR-01
AQ$_SCHEDULES_PRIMARY This index created : 16-APR-01
CATALOG SYNONYM
CHECK_CONST FUNCTION
