Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ora-32637: self cyclic

694333Apr 11 2009 — edited Apr 14 2009
i am confused the below query works absolutely fine at my home pc. i installed oracledb10g on my freind pc,
with all the necessary tables and try n run it there, its returning the above error.

is it a REGEDIT setting issue.? i set NLS_DATE_FORMAT to DD/MM/RRRR.
*_Date No. 1_*
SQL> select * from op_bal;
 
OP_COD OP_NAME            OP_DATE       OP_AMOUNT
------ ------------------ --------- -------------
OP0000 NATIONAL BANK      01-JAN-09      5000.000
 
*Date No.2*
SQL> SELECT
  2  EXN_DATE,
  3  EXN_NO,
  4  EXN_ACNT_CODE,
  5  EXPN_EI,
  6  DECODE(EXPN_EI,'E',EXN_AMOUNT) WITHDRAWAL,
  7  DECODE(EXPN_EI,'I',EXN_AMOUNT) DEPOSIT
  8  FROM EXPENSES_TXN, EXPENSE_MASTER
  9  WHERE EXN_ACNT_CODE = EXPN_CODE
 10  order by 1;
 
EXN_DATE         EXN_NO EXN_AC E    WITHDRAWAL       DEPOSIT
--------- ------------- ------ - ------------- -------------
01-MAR-09         2.000 AC0002 E      2000.000
10-MAR-09         7.000 AC0012 I                     500.000
15-MAR-09         5.000 AC0007 E        15.000
20-MAR-09         8.000 AC0012 I                     700.000
31-MAR-09         6.000 AC0008 E        30.000
01-APR-09         9.000 AC0013 I                     250.000
07-APR-09         1.000 AC0001 E       200.000
09-APR-09         4.000 AC0011 E        35.000
09-APR-09         3.000 AC0003 E        50.000
 
9 rows selected.

var p_op_date_frm  varchar2(10);
var p_op_date_upto varchar2(10);
exec :p_op_date_frm :=TO_DATE ('01-04-'||extract( year from sysdate),'dd-mm-yyyy');
exec :p_op_date_upto:= TO_DATE ('30-04-'||extract( year from sysdate),'dd-mm-yyyy');
SELECT op_date,  tno,
                  op_code,  ei,  withdrawal,  deposit,bal
    from (
              SELECT op_date,  tno,
                          op_code,  ei,  withdrawal,  deposit,bal
              FROM (SELECT   op_date, NULL tno, op_code op_code, NULL ei,
                        NULL withdrawal, op_amount deposit
                     FROM op_bal
                      UNION ALL
                     SELECT   exn_date, exn_no, exn_acnt_code, expn_ei,
                            DECODE (expn_ei, 'E', exn_amount) withdrawal,
                            DECODE (expn_ei, 'I', exn_amount) deposit
                      FROM expenses_txn, expense_master
                      WHERE exn_acnt_code = expn_code
                   ORDER BY 1)
            where  op_date <=:p_op_date_upto
             model
             partition by (extract ( year from op_date) yy)
             dimension by ( row_number()over(partition by extract ( year from op_date) order by op_date) rn )
             measures( op_date, tno,sum(deposit-withdrawal)over(order by op_date) bal,
                         op_code,  ei , withdrawal,  deposit,
                          max(case when op_date=:p_op_date_frm  then
                          rownum end )over(partition by extract(year from  op_date)) -1 fnd )ignore nav
           rules
           ( withdrawal[fnd[1]]= case when op_date[cv()]<:p_op_date_frm  then sum(withdrawal)[cv()>=rn]
                                      else withdrawal[cv()] end,
            deposit[fnd[1]]= case when op_date[cv()]<:p_op_date_frm   then sum(deposit)[cv()>=rn]
                                      else deposit[cv()] end,
             op_date[fnd[1]]=:p_op_date_frm ,
             ei[fnd[1]]=null,
             tno[fnd[1]]=null,
             op_code[fnd[1]]='OP0000',
             bal[any]= case when cv(rn)!=fnd[1] then   bal[cv()-1]+ nvl(deposit[cv()],0)- nvl(withdrawal[cv()],0)
                            else nvl(deposit[cv()],0)- nvl(withdrawal[cv()],0)end )
                )
   where op_date >= :p_op_date_frm;

i even tried n change like ;
exec :p_op_date_frm :=TO_DATE ('01/04/'||extract( year from sysdate),'dd/mm/yyyy');
exec :p_op_date_upto:= TO_DATE ('30/04/'||extract( year from sysdate),'dd/mm/yyyy');

the query returns this result;
OP_DATE           TNO OP_COD E WITHDRAWAL    DEPOSIT        BAL
---------- ---------- ------ - ---------- ---------- ----------
01/04/2009            OP0000         2045       6200       4155
01/04/2009          9 AC0013 I                   250       4405
07/04/2009          1 AC0001 E        200                  4205
09/04/2009          3 AC0003 E         50                  4155
09/04/2009          4 AC0011 E         35                  4120
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2009
Added on Apr 11 2009
23 comments
3,875 views