OraFAQ Forum: SQL & PL/SQL » Missing Record in running Total

Home » SQL & PL/SQL » SQL & PL/SQL » Missing Record in running Total (10.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Missing Record in running Total [message #655934]

Sat, 17 September 2016 06:45

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Hi,

I am working on a query but stuck.

My scenario is like this that on famous "scott.emp" table, i am working on running total.

My code is this

Select ename, sal, rtot
from (
select ename, sal, sum(sal) over (order by empno) as rtot
from emp)
where rtot<=3000

The result is

ENAME             SAL       RTOT
---------- ---------- ----------
SMITH             800        800
ALLEN            1600       2400

I required third record as well as it has contributed towards the target/analyzed amount of 3000.

hence i need this

ENAME             SAL SUM(SAL)OVER(ORDERBYEMPNO)
---------- ---------- --------------------------
SMITH             800                        800
ALLEN            1600                       2400
WARD             1250                       3650

Kindly help
1) to achieve the required result
2) by giving performance improvement tip

Thanks

Report message to a moderator

Re: Missing Record in running Total [message #655935 is a reply to message #655934]

Sat, 17 September 2016 06:51

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> with t as (
  2             select  ename,
  3                     sal,
  4                     sum(sal) over(order by empno) as rtot
  5               from  emp
  6            )
  7  select  ename,
  8          sal,
  9          rtot
 10    from  t
 11    where rtot - sal <= 3000
 12  /

ENAME             SAL       RTOT
---------- ---------- ----------
SMITH             800        800
ALLEN            1600       2400
WARD             1250       3650

SQL>

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655936 is a reply to message #655935]

Sat, 17 September 2016 07:02

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Amazing. Sometime we must think other way round.

Anyhow, thanks a lot.

Any tip for performance improvement? I have run the query on actual data but it is held.

Report message to a moderator

Re: Missing Record in running Total [message #655937 is a reply to message #655936]

Sat, 17 September 2016 07:05

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

Post actual SQL, explain plan, version, row counts, etc.

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655938 is a reply to message #655937]

Sat, 17 September 2016 07:38

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Select invno, invdate, dues, eod_due, amount, dated,  rtot, eod_due-rtot as leftover
from (
select invno, invdate, dated, sum(value) dues, bal(INVDATE,68) EOD_DUE, amount , 
	sum(amount) over (partition by invno order by invno, dated) rtot
from vcusthist1 a, invs b
where a.ccode=b.ccode
and a.ccode=68
and aors='S'
and b.invdate<a.dated
and invdate>='01-JUN-2016'
group by invno, invdate, dated, amount 
order by invno, invdate, dated)
where rtot - amount <= eod_due

More info (difficult to read) here

OPERATION 	 OBJECT_NAME 	 CARDINALITY 	 COST 

 SELECT STATEMENT 
   	   	 926 
    	
 VIEW 
   	 9849 	 926 
    	    	
 Filter Predicates 
    	    	    	
 EOD_DUE>=RTOT-AMOUNT 
    	    	
 SORT 
   	 9849 	 926 
    	    	    	
 WINDOW 
   	 9849 	 926 
    	    	    	    	
 HASH 
   	 9849 	 926 
    	    	    	    	    	
 HASH JOIN 
   	 9849 	 381 
    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	
 A.CCODE=B.CCODE 
    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	
 B.INVDATE
    	    	    	    	    	    	
 VIEW 
 INVS 	 210 	 87 
    	    	    	    	    	    	    	
 SORT 
   	 210 	 87 
    	    	    	    	    	    	    	    	
 HASH JOIN 
   	 210 	 86 
    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	
 B.PCODE=C.PCODE 
    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 PROD 	 371 	 3 
    	    	    	    	    	    	    	    	    	
 HASH JOIN 
   	 210 	 82 
    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	
 A.INVNO=B.INVNO 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 80 	 32 
    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 D.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 80 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.INVDATE>=TO_DATE('2015-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 
    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1S 	 40674 	 49 
    	    	    	    	    	    	
 VIEW 
 VCUSTHIST1 	 938 	 290 
    	    	    	    	    	    	    	
 SORT 
   	 938 	 290 
    	    	    	    	    	    	    	    	
 VIEW 
   	 938 	 256 
    	    	    	    	    	    	    	    	    	
 UNION-ALL 
   	   	   
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	   
    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	
 HASH JOIN 
   	 1076 	 82 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 409 	 32 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 409 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1S 	 40674 	 49 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 278 	 83 
    	    	    	    	    	    	    	    	    	    	    	
 HASH JOIN 
   	 278 	 82 
    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 106 	 32 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 106 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CORD='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1S 	 40674 	 49 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	 7 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1P 	 2 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 6 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0P 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_IN0P_CCODE_RET2 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV1P_INVNO 	 2 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	   
    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1P 	 2 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 6 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0P 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV0P_3_IDX 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CORD='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV1P_INVNO 	 2 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 COBAL>=0 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 CCODE=68 
    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
 COBAL<0 
    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 CCODE=68 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 2 	 2 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 VIEW 
 CASHP 	 2 	   
    	    	    	    	    	    	    	    	    	    	    	    	
 UNION-ALL 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHP1 	 44 	 21 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_IDX_CASHP1_CCODE 	 44 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0P 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INVCASHP<>0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV0P_3_IDX 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 CORD='D' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 639 	 67 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 VIEW 
 CASHR 	 639 	 65 
    	    	    	    	    	    	    	    	    	    	    	    	
 UNION-ALL 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 637 	 33 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 VIEW 
 index$_join$_059 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INTERNAL_FUNCTION(B.CODE) 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 HASH JOIN 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 ROWID=ROWID 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INLIST ITERATOR 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_CUST_CODE 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHR1 	 665 	 32 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 1 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INVCASHR<>0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 CORD='D' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASH_REC1 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AMT IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AMT<>0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_CASH_REC1_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASH_REC0 	 1 	 0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CASH_REC0_CASHNO 	 1 	 0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CASHNO=B.CASHNO 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 2 	 34 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 VIEW 
 CASHD 	 2 	 32 
    	    	    	    	    	    	    	    	    	    	    	    	
 UNION-ALL 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHD1 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 1 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INVCASHD<>0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 2 	 2 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 VIEW 
 CASHZ 	 2 	   
    	    	    	    	    	    	    	    	    	    	    	    	
 UNION-ALL 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHZ1 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0P 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INVCASHZ<>0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_IN0P_CCODE_RET2 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 RETURN2='N' 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHI 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 7 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHW 	 1 	 5 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHS 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 2 	 8 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	
 VIEW 
 CASHE 	 2 	 6 
    	    	    	    	    	    	    	    	    	    	    	    	
 UNION-ALL 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASHE1 	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CASH_REC0 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 EXPAMOUNT IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.EXPCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 EXPAMOUNT<>0 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	   
    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1S 	 3 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 34 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 32 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 1 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.RETURN2='Y' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV1S_INVNO 	 3 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	 35 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1S 	 3 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 34 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 32 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0S 	 1 	 30 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.RETURN2='Y' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CORD='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV1S_INVNO 	 3 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	 7 
    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1P 	 2 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 6 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0P 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_IN0P_CCODE_RET2 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.RETURN2='Y' 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV1P_INVNO 	 2 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	   
    	    	    	    	    	    	    	    	    	    	    	
 FILTER 
   	   	   
    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NULL IS NOT NULL 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV1P 	 2 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 6 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 OR 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='B' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 INV0P 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV0P_3_IDX 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.CORD='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.RETURN2='Y' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 IDX_INV1P_INVNO 	 2 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.INVNO=C.INVNO 
    	    	    	    	    	    	    	    	    	    	
 HASH 
   	 1 	 5 
    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 NESTED LOOPS 
   	 1 	 4 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 CUST 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CODE='C' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 PK_CUST_CCODE 	 1 	 1 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 A.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 MAN2 	 1 	 2 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 PROD 	 1 	 0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Filter Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 AND 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.CCODE=68 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 C.PTYPE='L' 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 SYS_C005494 	 1 	 0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 B.PCODE=C.PCODE 
    	    	    	    	    	    	    	    	    	    	    	    	
 TABLE ACCESS 
 MAN0 	 1 	 0 
    	    	    	    	    	    	    	    	    	    	    	    	    	
 INDEX 
 MAN0_MANNO 	 1 	 0 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 Access Predicates 
    	    	    	    	    	    	    	    	    	    	    	    	    	    	    	
 D.MANNO=B.MANNO 

 Other XML 

 {info} 

 info type="db_version" 

 10.2.0.1 

 info type="parse_schema" 

 "MOS" 

 info type="plan_hash" 

 2202021370 

 {hint} 

 IGNORE_OPTIM_EMBEDDED_HINTS 

 OPTIMIZER_FEATURES_ENABLE('10.2.0.1') 

 ALL_ROWS 

 OUTLINE_LEAF(@"SEL$389FFE88") 

 ELIMINATE_OUTER_JOIN(@"SEL$4") 

 OUTLINE_LEAF(@"SEL$5") 

 OUTLINE_LEAF(@"SEL$6") 

 OUTLINE_LEAF(@"SEL$7") 

 OUTLINE_LEAF(@"SEL$8") 

 OUTLINE_LEAF(@"SEL$9") 

 OUTLINE_LEAF(@"SEL$11") 

 OUTLINE_LEAF(@"SEL$12") 

 OUTLINE_LEAF(@"SET$2") 

 OUTLINE_LEAF(@"SEL$10") 

 OUTLINE_LEAF(@"SEL$69ECE7ED") 

 OUTLINE_LEAF(@"SEL$14") 

 OUTLINE_LEAF(@"SEL$15") 

 OUTLINE_LEAF(@"SEL$16") 

 OUTLINE_LEAF(@"SET$3") 

 OUTLINE_LEAF(@"SEL$13") 

 OUTLINE_LEAF(@"SEL$18") 

 OUTLINE_LEAF(@"SEL$19") 

 OUTLINE_LEAF(@"SET$4") 

 OUTLINE_LEAF(@"SEL$17") 

 OUTLINE_LEAF(@"SEL$21") 

 OUTLINE_LEAF(@"SEL$22") 

 OUTLINE_LEAF(@"SET$5") 

 OUTLINE_LEAF(@"SEL$20") 

 OUTLINE_LEAF(@"SEL$23") 

 OUTLINE_LEAF(@"SEL$24") 

 OUTLINE_LEAF(@"SEL$25") 

 OUTLINE_LEAF(@"SEL$27") 

 OUTLINE_LEAF(@"SEL$28") 

 OUTLINE_LEAF(@"SET$6") 

 OUTLINE_LEAF(@"SEL$26") 

 OUTLINE_LEAF(@"SEL$AE71B082") 

 ELIMINATE_OUTER_JOIN(@"SEL$29") 

 OUTLINE_LEAF(@"SEL$30") 

 OUTLINE_LEAF(@"SEL$31") 

 OUTLINE_LEAF(@"SEL$32") 

 OUTLINE_LEAF(@"SEL$33") 

 OUTLINE_LEAF(@"SET$1") 

 OUTLINE_LEAF(@"SEL$3") 

 OUTLINE_LEAF(@"SEL$5F9076AD") 

 MERGE(@"SEL$35") 

 OUTLINE_LEAF(@"SEL$2") 

 OUTLINE_LEAF(@"SEL$1") 

 OUTLINE(@"SEL$4") 

 OUTLINE(@"SEL$14") 

 OUTLINE(@"SEL$29") 

 OUTLINE(@"SEL$34") 

 OUTLINE(@"SEL$35") 

 NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") 

 NO_ACCESS(@"SEL$2" "B"@"SEL$2") 

 NO_ACCESS(@"SEL$2" "A"@"SEL$2") 

 LEADING(@"SEL$2" "B"@"SEL$2" "A"@"SEL$2") 

 USE_HASH(@"SEL$2" "A"@"SEL$2") 

 NO_ACCESS(@"SEL$3" "from$_subquery$_009"@"SEL$3") 

 INDEX(@"SEL$5F9076AD" "D"@"SEL$35" ("CUST"."CCODE")) 

 FULL(@"SEL$5F9076AD" "A"@"SEL$35") 

 FULL(@"SEL$5F9076AD" "B"@"SEL$35") 

 FULL(@"SEL$5F9076AD" "C"@"SEL$35") 

 LEADING(@"SEL$5F9076AD" "D"@"SEL$35" "A"@"SEL$35" "B"@"SEL$35" "C"@"SEL$35") 

 USE_NL(@"SEL$5F9076AD" "A"@"SEL$35") 

 USE_HASH(@"SEL$5F9076AD" "B"@"SEL$35") 

 USE_HASH(@"SEL$5F9076AD" "C"@"SEL$35") 

 SWAP_JOIN_INPUTS(@"SEL$5F9076AD" "C"@"SEL$35") 

 INDEX(@"SEL$33" "A"@"SEL$33" ("CUST"."CCODE")) 

 FULL(@"SEL$33" "B"@"SEL$33") 

 INDEX(@"SEL$33" "C"@"SEL$33" ("PROD"."PCODE")) 

 INDEX(@"SEL$33" "D"@"SEL$33" ("MAN0"."MANNO")) 

 LEADING(@"SEL$33" "A"@"SEL$33" "B"@"SEL$33" "C"@"SEL$33" "D"@"SEL$33") 

 USE_NL(@"SEL$33" "B"@"SEL$33") 

 USE_NL(@"SEL$33" "C"@"SEL$33") 

 USE_NL(@"SEL$33" "D"@"SEL$33") 

 INDEX(@"SEL$32" "A"@"SEL$32" ("CUST"."CCODE")) 

 INDEX(@"SEL$32" "B"@"SEL$32" ("INV0P"."CCODE" "INV0P"."CORD" "INV0P"."RETURN2")) 

 INDEX(@"SEL$32" "C"@"SEL$32" ("INV1P"."INVNO")) 

 LEADING(@"SEL$32" "A"@"SEL$32" "B"@"SEL$32" "C"@"SEL$32") 

 USE_NL(@"SEL$32" "B"@"SEL$32") 

 USE_NL(@"SEL$32" "C"@"SEL$32") 

 INDEX(@"SEL$31" "A"@"SEL$31" ("CUST"."CCODE")) 

 INDEX(@"SEL$31" "B"@"SEL$31" ("INV0P"."CCODE" "INV0P"."RETURN2")) 

 INDEX(@"SEL$31" "C"@"SEL$31" ("INV1P"."INVNO")) 

 LEADING(@"SEL$31" "A"@"SEL$31" "B"@"SEL$31" "C"@"SEL$31") 

 USE_NL(@"SEL$31" "B"@"SEL$31") 

 USE_NL(@"SEL$31" "C"@"SEL$31") 

 INDEX(@"SEL$30" "A"@"SEL$30" ("CUST"."CCODE")) 

 FULL(@"SEL$30" "B"@"SEL$30") 

 INDEX(@"SEL$30" "C"@"SEL$30" ("INV1S"."INVNO")) 

 LEADING(@"SEL$30" "A"@"SEL$30" "B"@"SEL$30" "C"@"SEL$30") 

 USE_NL(@"SEL$30" "B"@"SEL$30") 

 USE_NL(@"SEL$30" "C"@"SEL$30") 

 INDEX(@"SEL$AE71B082" "A"@"SEL$29" ("CUST"."CCODE")) 

 FULL(@"SEL$AE71B082" "B"@"SEL$29") 

 INDEX(@"SEL$AE71B082" "C"@"SEL$29" ("INV1S"."INVNO")) 

 LEADING(@"SEL$AE71B082" "A"@"SEL$29" "B"@"SEL$29" "C"@"SEL$29") 

 USE_NL(@"SEL$AE71B082" "B"@"SEL$29") 

 USE_NL(@"SEL$AE71B082" "C"@"SEL$29") 

 INDEX(@"SEL$26" "C"@"SEL$26" ("CUST"."CCODE")) 

 NO_ACCESS(@"SEL$26" "A"@"SEL$26") 

 LEADING(@"SEL$26" "C"@"SEL$26" "A"@"SEL$26") 

 USE_NL(@"SEL$26" "A"@"SEL$26") 

 INDEX(@"SEL$25" "C"@"SEL$25" ("CUST"."CCODE")) 

 FULL(@"SEL$25" "A"@"SEL$25") 

 LEADING(@"SEL$25" "C"@"SEL$25" "A"@"SEL$25") 

 USE_NL(@"SEL$25" "A"@"SEL$25") 

 INDEX(@"SEL$24" "C"@"SEL$24" ("CUST"."CCODE")) 

 FULL(@"SEL$24" "A"@"SEL$24") 

 LEADING(@"SEL$24" "C"@"SEL$24" "A"@"SEL$24") 

 USE_NL(@"SEL$24" "A"@"SEL$24") 

 INDEX(@"SEL$23" "C"@"SEL$23" ("CUST"."CCODE")) 

 FULL(@"SEL$23" "A"@"SEL$23") 

 LEADING(@"SEL$23" "C"@"SEL$23" "A"@"SEL$23") 

 USE_NL(@"SEL$23" "A"@"SEL$23") 

 INDEX(@"SEL$20" "C"@"SEL$20" ("CUST"."CCODE")) 

 NO_ACCESS(@"SEL$20" "A"@"SEL$20") 

 LEADING(@"SEL$20" "C"@"SEL$20" "A"@"SEL$20") 

 USE_NL(@"SEL$20" "A"@"SEL$20") 

 INDEX(@"SEL$17" "C"@"SEL$17" ("CUST"."CCODE")) 

 NO_ACCESS(@"SEL$17" "A"@"SEL$17") 

 LEADING(@"SEL$17" "C"@"SEL$17" "A"@"SEL$17") 

 USE_NL(@"SEL$17" "A"@"SEL$17") 

 INDEX(@"SEL$13" "C"@"SEL$13" ("CUST"."CCODE")) 

 NO_ACCESS(@"SEL$13" "A"@"SEL$13") 

 LEADING(@"SEL$13" "C"@"SEL$13" "A"@"SEL$13") 

 USE_NL(@"SEL$13" "A"@"SEL$13") 

 INDEX(@"SEL$10" "C"@"SEL$10" ("CUST"."CCODE")) 

 NO_ACCESS(@"SEL$10" "A"@"SEL$10") 

 LEADING(@"SEL$10" "C"@"SEL$10" "A"@"SEL$10") 

 USE_NL(@"SEL$10" "A"@"SEL$10") 

 INDEX(@"SEL$9" "CUST"@"SEL$9" ("CUST"."CCODE")) 

 INDEX(@"SEL$8" "CUST"@"SEL$8" ("CUST"."CCODE")) 

 INDEX(@"SEL$7" "A"@"SEL$7" ("CUST"."CCODE")) 

 INDEX(@"SEL$7" "B"@"SEL$7" ("INV0P"."CCODE" "INV0P"."CORD" "INV0P"."RETURN2")) 

 INDEX(@"SEL$7" "C"@"SEL$7" ("INV1P"."INVNO")) 

 LEADING(@"SEL$7" "A"@"SEL$7" "B"@"SEL$7" "C"@"SEL$7") 

 USE_NL(@"SEL$7" "B"@"SEL$7") 

 USE_NL(@"SEL$7" "C"@"SEL$7") 

 INDEX(@"SEL$6" "A"@"SEL$6" ("CUST"."CCODE")) 

 INDEX(@"SEL$6" "B"@"SEL$6" ("INV0P"."CCODE" "INV0P"."RETURN2")) 

 INDEX(@"SEL$6" "C"@"SEL$6" ("INV1P"."INVNO")) 

 LEADING(@"SEL$6" "A"@"SEL$6" "B"@"SEL$6" "C"@"SEL$6") 

 USE_NL(@"SEL$6" "B"@"SEL$6") 

 USE_NL(@"SEL$6" "C"@"SEL$6") 

 INDEX(@"SEL$5" "A"@"SEL$5" ("CUST"."CCODE")) 

 FULL(@"SEL$5" "B"@"SEL$5") 

 FULL(@"SEL$5" "C"@"SEL$5") 

 LEADING(@"SEL$5" "A"@"SEL$5" "B"@"SEL$5" "C"@"SEL$5") 

 USE_NL(@"SEL$5" "B"@"SEL$5") 

 USE_HASH(@"SEL$5" "C"@"SEL$5") 

 INDEX(@"SEL$389FFE88" "A"@"SEL$4" ("CUST"."CCODE")) 

 FULL(@"SEL$389FFE88" "B"@"SEL$4") 

 FULL(@"SEL$389FFE88" "C"@"SEL$4") 

 LEADING(@"SEL$389FFE88" "A"@"SEL$4" "B"@"SEL$4" "C"@"SEL$4") 

 USE_NL(@"SEL$389FFE88" "B"@"SEL$4") 

 USE_HASH(@"SEL$389FFE88" "C"@"SEL$4") 

 INDEX(@"SEL$12" "B"@"SEL$12" ("INV0P"."CCODE" "INV0P"."CORD" "INV0P"."RETURN2")) 

 INDEX(@"SEL$11" "A"@"SEL$11" ("CASHP1"."CCODE")) 

 INDEX(@"SEL$16" "B"@"SEL$16" ("CASH_REC1"."CCODE")) 

 INDEX(@"SEL$16" "A"@"SEL$16" ("CASH_REC0"."CASHNO")) 

 LEADING(@"SEL$16" "B"@"SEL$16" "A"@"SEL$16") 

 USE_NL(@"SEL$16" "A"@"SEL$16") 

 FULL(@"SEL$15" "B"@"SEL$15") 

 INDEX_JOIN(@"SEL$14" "B"@"SEL$14" ("CUST"."CCODE") ("CUST"."CODE")) 

 FULL(@"SEL$14" "A"@"SEL$14") 

 LEADING(@"SEL$14" "B"@"SEL$14" "A"@"SEL$14") 

 USE_NL(@"SEL$14" "A"@"SEL$14") 

 FULL(@"SEL$19" "B"@"SEL$19") 

 FULL(@"SEL$18" "A"@"SEL$18") 

 INDEX(@"SEL$22" "B"@"SEL$22" ("INV0P"."CCODE" "INV0P"."RETURN2")) 

 FULL(@"SEL$21" "A"@"SEL$21") 

 FULL(@"SEL$28" "A"@"SEL$28") 

 FULL(@"SEL$27" "A"@"SEL$27")

Version: 10.2.0.1.0

It took more than 30 minutes when i changed '01-JUN-2016' to '01-JUN-2011' in the query

[EDITED by LF: applied [spoiler] tags]

[Updated on: Sat, 17 September 2016 12:42] by Moderator

Report message to a moderator

Re: Missing Record in running Total [message #655939 is a reply to message #655938]

Sat, 17 September 2016 08:56

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

Please format explain plan, so it is readable.

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655940 is a reply to message #655939]

Sat, 17 September 2016 09:07

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

Also, your code executes function bal(invdate,68) for each group even though it might be rejected later. Move it out to main query:

select  invno,
        invdate,
        dues,
        bal(invdate,68) eod_due,
        amount,
        dated,
        rtot,
        eod_due - rtot as leftover
  from  (
         select  invno,
                 invdate,
                 dated,
                 sum(value) dues,
--                 bal(invdate,68) eod_due,
                 amount, 
	         sum(amount) over(partition by invno order by invno,dated) rtot
           from  vcusthist1 a,
                 invs b
           where a.ccode = b.ccode
             and a.ccode = 68
             and aors = 'S'
             and b.invdate < a.dated
             and invdate >= DATE '2016-06-01'
          group by invno,
                   invdate,
                   dated,
                   amount 
       )
  where rtot - amount <= eod_due
  order by invno,
           invdate,
           dated
/

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655941 is a reply to message #655940]

Sat, 17 September 2016 09:25

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

I have uploaded csv file .

rename it to html.

It will show you the explain plan in html format

Attachment: explain plan.csv
(Size: 454.25KB, Downloaded 1251 times)

Report message to a moderator

Re: Missing Record in running Total [message #655942 is a reply to message #655940]

Sat, 17 September 2016 09:51

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

Oops,

I missed eod_due is used in where clause and therefore it can't be moved out to main query.
Anyway, based on explain plan, both vcusthist1 and invs are complex views. Also, it is not clear how complex/time consuming user defined function BAL is. Run:

         select  invno,
                 invdate,
                 dated,
                 sum(value) dues,
                 bal(invdate,68) eod_due,
                 amount, 
	         sum(amount) over(partition by invno order by invno,dated) rtot
           from  vcusthist1 a,
                 invs b
           where a.ccode = b.ccode
             and a.ccode = 68
             and aors = 'S'
             and b.invdate < a.dated
             and invdate >= DATE '2016-06-01'
          group by invno,
                   invdate,
                   dated,
                   amount 
/

and

         select  invno,
                 invdate,
                 dated,
                 sum(value) dues,
--                 bal(invdate,68) eod_due,
                 amount, 
	         sum(amount) over(partition by invno order by invno,dated) rtot
           from  vcusthist1 a,
                 invs b
           where a.ccode = b.ccode
             and a.ccode = 68
             and aors = 'S'
             and b.invdate < a.dated
             and invdate >= DATE '2016-06-01'
          group by invno,
                   invdate,
                   dated,
                   amount 
/

Difference in time divided by number of returned rows will give you function BAL average run time.

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655943 is a reply to message #655942]

Sat, 17 September 2016 10:30

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Yes, its BAL function which is taking lots of time.

I have tried to taken it out of the main query so that it should run ONCE for main set (but i think i lag expertise handling it).
Here is my try

Select invno, invdate, dues,  EOD_DUE,  amount, dated,  rtot, eod_due-rtot leftover
from (
Select c.invno, invdate, dues,   eod_due, amount, dated,  rtot
from (
select invno, invdate, dated, sum(value) dues,  amount , 
	sum(amount) over (partition by invno order by invno, dated) rtot
from vcusthist1 a, invs b
where a.ccode=b.ccode
and a.ccode=68
and aors='S'
and b.invdate<a.dated
and invdate>='01-JUN-2015'
group by invno, invdate, dated, amount 
order by invno, invdate, dated) c, (Select invno, ccode, bal(invdate, ccode) as eod_due from inv0s where ccode=68 and invdate>='01-JUN-2015') d
where c.invno=d.invno
)
where rtot - amount <= eod_due

Report message to a moderator

Re: Missing Record in running Total [message #655945 is a reply to message #655943]

Sat, 17 September 2016 14:55

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

So you have to work on tuning the function, or possibly replace it with SQL which would eliminate costly context switched.

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655946 is a reply to message #655945]

Sat, 17 September 2016 19:47

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

BAL is a simple function which calcaultes of the balance of ccode by working on vcusthist1. It is something like this

Select nvl(sum(decode(AORS, 'A', amount, 0)),0) - abs(nvl(sum(decode(AORS, 'S', amount, 0)),0))
		FROM Vcusthist1
		where ccode = pin_ccode;

I am trying otherway round with the help of this query

Select invno, invdate, sum(dues),   sum(eod_due), dated,  sum(amount), sum(rtot)
from (
select invno, 
	invdate, 
	dated, 
	0 as EOD_DUE, 
	sum(value) dues,  
	amount , 
	sum(amount) over (partition by invno order by invno, dated) rtot
from vcusthist1 a, invs b
where a.ccode=b.ccode
and a.ccode=68
and aors='S'
and b.invdate<a.dated
and invdate>='15-AUG-2016'
group by invno, invdate, dated, amount 
UNION ALL
Select 	invno, 
	invdate, 
	to_date(null),  
	bal(invdate, ccode) as eod_due , 
	0, 
	0, 
	0
from inv0s 
where ccode=68 
and invdate>='15-AUG-2016'
)
group by invno, invdate , dated
order by invno, invdate

The first records are as follow:

   INVNO INVDATE    SUM(DUES) SUM(EOD_DUE) DATED     SUM(AMOUNT)  SUM(RTOT)
-------- --------- ---------- ------------ --------- ----------- ----------
   15428 17-AUG-16      40250            0 20-AUG-16       50000      50000
   15428 17-AUG-16      40250            0 22-AUG-16       78000     128000
   15428 17-AUG-16      40250            0 23-AUG-16       25000     153000
   15428 17-AUG-16      40250            0 24-AUG-16       35000     188000
   15428 17-AUG-16      40250            0 09-SEP-16       50000     238000
   15428 17-AUG-16      40250            0 10-SEP-16       50000     288000
   15428 17-AUG-16          0    156886.51                     0          0

Cant figure out how to make it as per following requirement

INVNO	INVDATE	DUES	EOD_DUE	DATED	SUM(AMOUNT)	SUM(RTOT)
-----	---------	----------	----------	---------	-----------	----------
15428	17-Aug-16	40250	156886.51	20-Aug-16	50000	50000
15428	17-Aug-16	40250	156886.51	22-Aug-16	78000	128000
15428	17-Aug-16	40250	156886.51	23-Aug-16	25000	153000
15428	17-Aug-16	40250	156886.51	24-Aug-16	35000	188000
15428	17-Aug-16	40250	156886.51	09-Sep-16	50000	238000
15428	17-Aug-16	40250	156886.51	10-Sep-16	50000	288000

Report message to a moderator

Re: Missing Record in running Total [message #655955 is a reply to message #655946]

Sun, 18 September 2016 12:09

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

bluetooth420 wrote on Sat, 17 September 2016 20:47

BAL is a simple function which calcaultes of the balance of ccode by working on vcusthist1. It is something like this
Select nvl(sum(decode(AORS, 'A', amount, 0)),0) - abs(nvl(sum(decode(AORS, 'S', amount, 0)),0))
		FROM Vcusthist1
		where ccode = pin_ccode;

It doesn't add up. BAL has 2 parameters while the above query has one (I am guessing pin_ccode is IN parameter for ccode). About query itself. It fetches all rows where ccode = pin_ccode regardless of AORS but needs only AORS in ('A','S'). Can AORS have values other than A or S? If so, query is inefficient. Use:

select  nvl(sum(decode(AORS, 'A', amount, 0)),0) - abs(nvl(sum(decode(AORS, 'S', amount, 0)),0))
  from  Vcusthist1
  where ccode = pin_ccode
    and AORS in ('A','S');

and have index on CCODE,AORS,AMOUNT.

SY.

[Updated on: Sun, 18 September 2016 12:10]

Report message to a moderator

Re: Missing Record in running Total [message #655969 is a reply to message #655955]

Sun, 18 September 2016 15:24

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Sorry, dont know why i edited it while posting.
Here is original query

Select nvl(sum(decode(AORS, 'A', amount, 0)),0) - abs(nvl(sum(decode(AORS, 'S', amount, 0)),0))
		into a
		FROM Vcusthist1
		where	(dated <pin_dated+1 or dated is null) and ccode = pin_ccode;
       RETURN a;

AORS will not have value toher than A or S (hence the column name: A OR S)

Report message to a moderator

Re: Missing Record in running Total [message #655970 is a reply to message #655969]

Sun, 18 September 2016 16:26

Solomon Yakobson
Messages: 3270
Registered: January 2010
Location: Connecticut, USA

Senior Member

OK. Is there an index on CCODE,DATES,AMOUNT?

SY.

Report message to a moderator

Re: Missing Record in running Total [message #655974 is a reply to message #655970]

Mon, 19 September 2016 01:47

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

SQL> create index idx_vcusthist1_ccode on vcusthist1(ccode);
create index idx_vcusthist1_ccode on vcusthist1(ccode)
                                     *
ERROR at line 1:
ORA-01702: a view is not appropriate here

Report message to a moderator

Re: Missing Record in running Total [message #655975 is a reply to message #655974]

Mon, 19 September 2016 03:22

cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester

Senior Member

So post the query for the view.

Report message to a moderator

Re: Missing Record in running Total [message #655978 is a reply to message #655975]

Mon, 19 September 2016 06:37

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

  CREATE OR REPLACE FORCE VIEW "MOS"."VCUSTHIST1" ("MYUSER", "TTYPE", "CCODE", "CODE", "CNAME", "DATED", "DETAIL", "AMOUNT", "AORS", "REF", "REMARKS") AS 
  SELECT "MYUSER","TTYPE","CCODE","CODE","CNAME","DATED","DETAIL","AMOUNT","AORS","REF","REMARKS" FROM (
SELECT 	MYUSER,	-- Checking Sales Invoice On cash and ON Credit
 '1' "TTYPE",
 A.cCODE,
 A.code,
 A.CNAME,
 B.INVDATE "DATED",
 DECODE(B.CORD, 'C', 'Sales Invoice - On Cash', 'D', 'Sales Invoice - On Credit') "DETAIL",
 SUM(QTY*UPRICE) "AMOUNT",
 'A' "AORS",
  B.INVNO "REF",
  B.REMARKS "REMARKS"
FROM CUST A, INV0S B, INV1S C
WHERE A.cCODE = b.cCODE(+)
AND   B.INVNO = C.INVNO
and   A.CODE IN ('C', 'B')
and return2='N'
GROUP BY MYUSER,  A.CCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 DECODE(B.CORD, 'C', 'Sales Invoice - On Cash', 'D', 'Sales Invoice - On Credit'),
 'A',
 b.INVNO,
 B.REMARKS
			UNION ALL
SELECT  MYUSER,   --Check Cash inflow on cash sales
 '2',
 A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 'Cash Received against Cash Sales Inovice' "CODE",
  (SUM(QTY*UPRICE) - (sum(nvl(invcashd,0))/count(*))),
 'S',
 B.Invno,
 B.REMARKS
FROM CUST A, INV0S B, INV1S C
WHERE A.CCODE = b.CCODE
AND   B.INVNO = C.INVNO
and   A.CODE IN ('C', 'B')
AND   B.CORD='C'
and return2='N'
GROUP BY   MYUSER, A.cCODE,
 a.code,
 A.CNAME,
 A.CCODE,
 B.INVDATE,
 'S',
  b.Invno,
 b.remarks
			UNION ALL
SELECT MYUSER, 				-- cHECKING PURCHASE INOVICE ON CASH AND CREDIT
 '3',
  A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE "DATED",
 DECODE(B.CORD, 'C', 'Purchase Invoice - On Cash', 'D', 'Purchase Invoice - On Credit') "CODE",
 SUM(QTY*UPRICE) "AMOUNT",
 'S' "AORS",
 B.INVNO "REF",
 B.REMARKS
FROM CUST A, INV0P B, INV1P C
WHERE A.CCODE = b.CCODE
AND   B.INVNO = C.INVNO
and b.RETURN2='N'
and   A.CODE IN ('C', 'B')
GROUP BY   MYUSER, A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 DECODE(B.CORD, 'C', 'Purchase Invoice - On Cash', 'D', 'Purchase Invoice - On Credit') ,
 'S',
 b.INVNO,
 B.REMARKS
			UNION ALL
SELECT MYUSER, 				--Check Cash OUTFLOW ON cash PURCHASE
 '4',
 A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 'Cash PAID against Cash PURCHASE INVOICE' ,
 (SUM(QTY*UPRICE) - (sum(nvl(invcashz,0))/count(*))),
 'A',
 B.Invno,
 B.REMARKS
FROM CUST A, INV0P B, INV1P C
WHERE A.CCODE = b.CCODE
AND   B.INVNO = C.INVNO
and   A.CODE IN ('C', 'B')
and b.RETURN2='N'
AND   B.CORD='C'
GROUP BY   MYUSER, A.cCODE,
 a.code,
 A.CNAME,
 A.CCODE,
 B.INVDATE,
 'A',
  b.Invno,
 b.remarks
					UNION ALL --OPENING BALANCE FOR RECEIVEABLES
SELECT to_char(null), '5', CCODE, CODE, CNAME, TO_DATE(NULL), 'Starting Balance', COBAL, 'A', to_number(NULL), TO_CHAR(NULL)
FROM CUST
WHERE COBAL>=0
					UNION ALL --OPENING BALANCE FOR PAYABLES
SELECT to_char(null), '6', CCODE, CODE, CNAME, TO_DATE(NULL), 'Starting Balance', ABS(COBAL), 'S', to_number(NULL), TO_CHAR(NULL)
FROM CUST
WHERE COBAL<0
					UNION ALL --CASH PAYMENTS
SELECT MYUSER, '7', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'Cash Payments', A.AMT, 'A', A.cashno, A.remarks
FROM CASHP A, CUST C
where a.CCODE = c.CCODE
					UNION ALL --CASH RECEIPTS
SELECT MYUSER, '8', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'Cash Receipts', A.AMT, 'S', A.cashno, A.remarks
FROM CASHR A, CUST C
where a.CCODE = c.CCODE
					UNION ALL --SALES DISCOUNT
SELECT MYUSER, '9', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'Sales Discount', A.AMT, 'S', A.cashno, A.remarks
FROM CASHD A, CUST C
where a.CCODE = c.CCODE
					UNION ALL -- PURCHASE DISCOUNT
SELECT MYUSER, '10', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'Purchase Discount', A.AMT, 'A', A.cashno, A.remarks
FROM CASHZ A, CUST C
where a.CCODE = c.CCODE
					UNION ALL --CASH INVESTMENT
SELECT MYUSER, '11', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'Cash Investment', A.AMT, 'S', A.cashno, A.remarks
FROM CASHI A, CUST C
where a.CCODE = c.CCODE
					UNION ALL --CASH Withdrawer
SELECT MYUSER, '12', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'Cash WITHDRAWER', A.AMT, 'A', A.cashno, A.remarks
FROM CASHW A, CUST C
where a.CCODE = c.CCODE
					UNION ALL --OTHER INCOME
SELECT MYUSER, '13', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'OTHER INCOME', A.AMT, 'A', A.cashno, A.remarks
FROM CASHS A, CUST C
where a.CCODE = c.CCODE
					UNION ALL -- EXPENSES
SELECT MYUSER, '14', C.CCODE, C. CODE, CNAME, A.CASHDATED, 'EXPENSES', A.AMT, 'S', A.cashno, A.remarks
FROM CASHE A, CUST C
where a.CCODE = c.CCODE
					UNION ALL --Checking PURCHASE RETURN On cash and ON Credit
SELECT MYUSER, 		--
 '15' ,
 A.cCODE,
 A.code,
 A.CNAME,
 B.INVDATE "DATED",
 DECODE(B.CORD, 'C', 'Purchase Return - On Cash', 'D', 'Purchase Return - On Credit') "DETAIL",
 SUM(QTY*UPRICE) "AMOUNT",
 'A' "AORS",
  B.INVNO "REF",
  B.REMARKS "REMARKS"
FROM CUST A, INV0S B, INV1S C
WHERE A.cCODE = b.cCODE(+)
AND   B.INVNO = C.INVNO
and b.RETURN2='Y'
and   A.CODE IN ('C', 'B')
GROUP BY   MYUSER, A.CCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 DECODE(B.CORD, 'C', 'Purchase Return - On Cash', 'D', 'Purchase Return - On Credit'),
 'A',
 b.INVNO,
 B.REMARKS
			UNION ALL
SELECT MYUSER, 			--Check Cash inflow on cash PURCHASE RETURN
 '16',
 A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 'Cash Received against Cash Purchase Return' "CODE",
 SUM(QTY*UPRICE),
 'S',
 B.Invno,
 B.REMARKS
FROM CUST A, INV0S B, INV1S C
WHERE A.CCODE = b.CCODE
AND   B.INVNO = C.INVNO
and b.RETURN2='Y'
and   A.CODE IN ('C', 'B')
AND   B.CORD='C'
GROUP BY   MYUSER, A.cCODE,
 a.code,
 A.CNAME,
 A.CCODE,
 B.INVDATE,
 'S',
  b.Invno,
 b.remarks
			UNION ALL
SELECT MYUSER, 				-- cHECKING SALE RETURN  CASH AND CREDIT
 '17',
  A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE "DATED",
 DECODE(B.CORD, 'C', 'Sale Return - On Cash', 'D', 'Sale Return - On Credit') "CODE",
 SUM(QTY*UPRICE) "AMOUNT",
 'S' "AORS",
 B.INVNO "REF",
 B.REMARKS
FROM CUST A, INV0P B, INV1P C
WHERE A.CCODE = b.CCODE
AND   B.INVNO = C.INVNO
and b.RETURN2='Y'
and   A.CODE IN ('C', 'B')
GROUP BY   MYUSER, A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 DECODE(B.CORD, 'C', 'Sale Return - On Cash', 'D', 'Sale Return - On Credit') ,
 'S',
 b.INVNO,
 B.REMARKS
			UNION ALL
SELECT MYUSER,  				--Check Cash OUTFLOW ON cash SALE RETURN
 '18',
 A.cCODE,
 A.CODE,
 A.CNAME,
 B.INVDATE,
 'Cash PAID against Cash Sale Return' ,
 SUM(QTY*UPRICE),
 'A',
 B.Invno,
 B.REMARKS
FROM CUST A, INV0P B, INV1P C
WHERE A.CCODE = b.CCODE
AND   B.INVNO = C.INVNO
and   A.CODE IN ('C', 'B')
and b.RETURN2='Y'
AND   B.CORD='C'
GROUP BY   MYUSER, A.cCODE,
 a.code,
 A.CNAME,
 A.CCODE,
 B.INVDATE,
 'A',
  b.Invno,
 b.remarks
			UNION ALL
SELECT MYUSER,  				--check liability due to manfacuring billing process
 '19',
 a.cCODE,
 code,
 A.CNAME,
 d.manDATEd,
 c.pname ,
 SUM(nvl(QTY,0)*nvl(UPRICE,0)),
 'S',
 B.manno,
 'Bill no' || lotno
FROM CUST A, man2 b, prod c, man0 d
WHERE b.pcode=c.pcode and a.CCODE = c.CCODE and d.manno=b.manno
and   A.CODE IN ('C') and c.ptype='L'
GROUP BY   MYUSER, A.cCODE,
 a.code,
 A.CNAME,
 A.CCODE,
 d.manDATEd,
 c.pname,
'S',
  b.manno , lotno
)
order by CCODE, decode(dated, null, 0, 1), dated, DETAIL, REF;

Report message to a moderator

Re: Missing Record in running Total [message #655980 is a reply to message #655978]

Mon, 19 September 2016 07:22

cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well that wins the award for the most unions seen in a single query.
You do realise that a lot of the individual selects that make that view select the same/overlapping rows as each other? Are you sure that's correct?

You can see from the view queries which columns will need the indexes but even after you've added them I doubt the performance is going to be great.
I also suspect that some of the tables referenced by the view are in fact views: cashp, cashr, cashi etc.
In which case you've got so many layers of complication that the optimizer is going to have a really hard time and you really need a new select that goes straight to the underlying tables.

Report message to a moderator

Re: Missing Record in running Total [message #655983 is a reply to message #655980]

Mon, 19 September 2016 07:35

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Well, it was my first design and it worked well even with all flaws you mentioned. (and at least able to give me award today as well from you Wink

)

cashp, cashr, cashi are NOT views but tables catering different functions in the system rather all are tables

Need to build vcusthist1 in order to compute balance. Created BAL function to compute balance of ccode on any particular date. AORS were debits and credits for me.

Underlying tables has indexes.

Can anything be done with http://www.orafaq.com/forum/mv/msg/202011/655946/#msg_655946 ?

Report message to a moderator

Re: Missing Record in running Total [message #655986 is a reply to message #655983]

Mon, 19 September 2016 08:36

cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well the structure of the queries with the set of cash tables strongly implies they should be one table. When I see I set of tables with similar structures that are getting unioned I think somebody is doing partitioning without paying for oracle partitioning. If that's the case - don't. It's a waste of time. It's fine when you're queried one of them but as soon as start having to query unions the performance plummets.
You're doing 18 separate accesses of cust every time you query that view plus 4 each on each of the INV tables and then 8 cash tables, that'll never be efficient.
You could try a materialized view but it won't refresh on commit.

Other thoughts:
1) Are the INV1 tables necessary? You're joining to them but nothing else.
2) The two outer joins are currently acting as inner joins. You need to outer-join to inv1s as well to make them work.
3) Not sure what the point of your link is. We know bal is slow and it's slow because of the view. You can certainly merge the bal select into the outer select and it might help a bit but unless you simplify that view I suspect it'll always be slow.

Report message to a moderator

Re: Missing Record in running Total [message #655988 is a reply to message #655986]

Mon, 19 September 2016 09:13

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Dear Cookie, Re-Designing the tables will be somewhat 360 degree turn for me as the things has gone too far with respect to working and reporting.

Why i chosen to use UNIONS? because very initially I started my work with
Select abc, (select ..from), (select ..from)
from a_table;
It was creating slowness. I changed to unions and it worked.

The view is only needed to be viewed in case of balance or detailed history so never had any major problem but with current report.

Your thoughts reply:
1) INV1 are very much necessary as the are basic INVOICES tables (master and detail)
2) OUter joins are also needed to bring up master record in case of missing child record.
3) With refer to my link, The BAL function is being called on every record hence the slowness. I somehow tried to manage to run BAL function where necessary but failed to allocate it with every record.

Report message to a moderator

Re: Missing Record in running Total [message #655989 is a reply to message #655988]

Mon, 19 September 2016 09:43

cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester

Senior Member

bluetooth420 wrote on Mon, 19 September 2016 15:13

Dear Cookie, Re-Designing the tables will be somewhat 360 degree turn for me as the things has gone too far with respect to working and reporting.

Why i chosen to use UNIONS? because very initially I started my work with
Select abc, (select ..from), (select ..from)
from a_table;
It was creating slowness. I changed to unions and it worked.

That's really irrelevant to my point. If they have similar structure and related data and can be unioned they should probably be a single table, the fact you can use inline views instead of union doesn't change that

bluetooth420 wrote on Mon, 19 September 2016 15:13

The view is only needed to be viewed in case of balance or detailed history so never had any major problem but with current report.

So you have different tables, with similar structures for different payement types?

bluetooth420 wrote on Mon, 19 September 2016 15:13

Your thoughts reply:
1) INV1 are very much necessary as the are basic INVOICES tables (master and detail)
2) OUter joins are also needed to bring up master record in case of missing child record.
3) With refer to my link, The BAL function is being called on every record hence the slowness. I somehow tried to manage to run BAL function where necessary but failed to allocate it with every record.

1) fair enough
2) You better fix them then
3) As I said above you can join the original query to the view instead of using the function and it'll probably help a bit, I doubt it'll be fast though. I have no idea what the 2nd sentence means - you need to show the actual code you tried.

Report message to a moderator

Re: Missing Record in running Total [message #656030 is a reply to message #655934]

Wed, 21 September 2016 02:13

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

I made my report other way round.

I added a column EOD_DUE.
In oracle reports, i updated EOD_DUE with BAL function in after parameter report trigger.

Then used EOD_DUE as column in the query to generate my report at good speed

Thanks to CM and SY.

(P.S. Mutating error was faced but solved by creating a temp table, inserting values in it then copying value from temp_table and then deleting temp_table. All done in after parameter report trigger)

Report message to a moderator

Previous Topic:	Materialised views not refreshing
Next Topic:	Count Adult Age in Range Time in 2016

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 13:39:18 CDT 2024