Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    7

    Unanswered: Inconsistent Data

    When i am running this below query, i am not geting the latest tsi_pos_num which i have given in the order by clause eventhough the record is there. Any idea??


    SELECT *
    FROM
    (SELECT rownum rnum,
    TABLE_NAME.*
    FROM
    (SELECT /*+ ORDERED use_nl (tsi, po, instr, otsi, acnt_dr,bp) index (tsi idx_tsi_excndt) */

    tsi.tsi_pos_typ,
    tsi.tsi_pos_num,
    tsi.tsi_ou_id,
    decode(acnt_dr.acnt_usg, 7, acnt_dr.ref_acnt_num, acnt_dr.ext_ref_num_2),
    decode(nvl(tsi.stlmnt_acnt_pos_num, ''), '', tsi.ordrng_cust_account, tsi.stlmnt_acnt_pos_num),
    tsi.stlmnt_acnt_pos_typ,
    tsi.sqnc_num,
    po.mirn,
    po.po_dt,
    tsi.val_dt,
    tsi.exctn_dt date1,
    po.stat stat1,
    tsi.stat stat2,
    tsi.crncy,
    tsi.amt amt1,
    po.ref_num,
    acnt_dr.bp_id,
    tsi.stlmnt_acnt_nm,
    po.reltd_ref_num,
    po.org_msg,
    po.user_id,
    po.po_prdtct,
    instr.shrt_name,
    po.exctn_dt date2,
    po.amt amt2,
    po.org_msg_sys,
    tsi.is_bulk,
    tsi.evnt,
    otsi.uti_serial,
    po.internal_stat
    FROM txn_stlmnt_instr tsi,
    payment_order po,
    instrument instr,
    outgoing_tsi otsi,
    account acnt_dr,
    business_partner bp
    WHERE(tsi.tsi_pos_typ = 25
    AND(instr.instrmnt_id = po.po_prdtct)
    AND(po.po_pos_typ = tsi.tsi_pos_typ
    AND po.po_pos_num = tsi.tsi_pos_num)
    AND(otsi.tsi_pos_typ = tsi.tsi_pos_typ
    AND otsi.tsi_pos_num = tsi.tsi_pos_num
    AND otsi.sqnc_num = tsi.sqnc_num)
    AND(tsi.stlmnt_dr_flg = 1)
    AND(tsi.stat = to_number('23'))
    AND(acnt_dr.pos_type(+) = tsi.stlmnt_acnt_pos_typ
    AND acnt_dr.pos_id(+) = to_number(nvl(tsi.stlmnt_acnt_pos_num, '0'))
    AND acnt_dr.sub_acnt_id(+) = 1)
    AND(acnt_dr.bp_id = bp.bp_id(+)))
    AND(tsi.tsi_ou_id LIKE 'GNVNLDBN00' || '%')
    AND(tsi.tsi_pos_typ > 0 OR(tsi.tsi_pos_typ = 0
    AND tsi.tsi_pos_num < 0) OR(tsi.tsi_pos_typ = 0
    AND tsi.tsi_pos_num = 0
    AND tsi.sqnc_num > 0)))
    TABLE_NAME
    WHERE rownum <= 22)
    WHERE rnum > 0
    ORDER BY tsi_pos_typ desc,
    tsi_pos_num DESC,
    sqnc_num ASC

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are you using Oracle?
    If so, you may want to ask in Oracle forum.
    Oracle - dBforums


    Anyhow, you specified the following condition in subquery
    WHERE rownum <= 22)
    and no ORDER BY was specified in the subquery.

    So, it wouldn't be guranteed that the first row of outside ORDER BY was included in the result of subquery.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please read this: http://www.dbforums.com/oracle/10316...s-posters.html regarding proper formatting of SQL statements in your post

Posting Permissions

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