DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: can we decode a decoded statement

  1. #1
    Join Date
    Jun 2001
    Posts
    109

    Unhappy

    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 )



  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

  3. #3
    Join Date
    Jun 2001
    Posts
    109

    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.



  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width