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