Home » SQL & PL/SQL » SQL & PL/SQL » Missing Record in running Total (10.2.0)
Missing Record in running Total [message #655934] Sat, 17 September 2016 06:45 Go to next message
bluetooth420
Messages: 136
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
Re: Missing Record in running Total [message #655935 is a reply to message #655934] Sat, 17 September 2016 06:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
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.
Re: Missing Record in running Total [message #655936 is a reply to message #655935] Sat, 17 September 2016 07:02 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
Smile

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.
Re: Missing Record in running Total [message #655937 is a reply to message #655936] Sat, 17 September 2016 07:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
Post actual SQL, explain plan, version, row counts, etc.

SY.
Re: Missing Record in running Total [message #655938 is a reply to message #655937] Sat, 17 September 2016 07:38 Go to previous messageGo to next message
bluetooth420
Messages: 136
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

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
Please format explain plan, so it is readable.

SY.
Re: Missing Record in running Total [message #655940 is a reply to message #655939] Sat, 17 September 2016 09:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
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.
Re: Missing Record in running Total [message #655941 is a reply to message #655940] Sat, 17 September 2016 09:25 Go to previous messageGo to next message
bluetooth420
Messages: 136
Registered: November 2011
Senior Member
I have uploaded csv file .

rename it to html.


It will show you the explain plan in html format
Re: Missing Record in running Total [message #655942 is a reply to message #655940] Sat, 17 September 2016 09:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
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.
Re: Missing Record in running Total [message #655943 is a reply to message #655942] Sat, 17 September 2016 10:30 Go to previous messageGo to next message
bluetooth420
Messages: 136
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
Re: Missing Record in running Total [message #655945 is a reply to message #655943] Sat, 17 September 2016 14:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
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.
Re: Missing Record in running Total [message #655946 is a reply to message #655945] Sat, 17 September 2016 19:47 Go to previous messageGo to next message
bluetooth420
Messages: 136
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
Re: Missing Record in running Total [message #655955 is a reply to message #655946] Sun, 18 September 2016 12:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
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 Go to previous messageGo to next message
bluetooth420
Messages: 136
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)
Re: Missing Record in running Total [message #655970 is a reply to message #655969] Sun, 18 September 2016 16:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
OK. Is there an index on CCODE,DATES,AMOUNT?

SY.
Re: Missing Record in running Total [message #655974 is a reply to message #655970] Mon, 19 September 2016 01:47 Go to previous messageGo to next message
bluetooth420
Messages: 136
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
Re: Missing Record in running Total [message #655975 is a reply to message #655974] Mon, 19 September 2016 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
So post the query for the view.
Re: Missing Record in running Total [message #655978 is a reply to message #655975] Mon, 19 September 2016 06:37 Go to previous messageGo to next message
bluetooth420
Messages: 136
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;
 
Re: Missing Record in running Total [message #655980 is a reply to message #655978] Mon, 19 September 2016 07:22 Go to previous messageGo to next message
cookiemonster
Messages: 13011
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.
Re: Missing Record in running Total [message #655983 is a reply to message #655980] Mon, 19 September 2016 07:35 Go to previous messageGo to next message
bluetooth420
Messages: 136
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 ?
Re: Missing Record in running Total [message #655986 is a reply to message #655983] Mon, 19 September 2016 08:36 Go to previous messageGo to next message
cookiemonster
Messages: 13011
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.
Re: Missing Record in running Total [message #655988 is a reply to message #655986] Mon, 19 September 2016 09:13 Go to previous messageGo to next message
bluetooth420
Messages: 136
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.


Re: Missing Record in running Total [message #655989 is a reply to message #655988] Mon, 19 September 2016 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13011
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.
Re: Missing Record in running Total [message #656030 is a reply to message #655934] Wed, 21 September 2016 02:13 Go to previous message
bluetooth420
Messages: 136
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)
Previous Topic: Materialised views not refreshing
Next Topic: Count Adult Age in Range Time in 2016
Goto Forum:
  


Current Time: Sun Feb 18 13:15:05 CST 2018

Total time taken to generate the page: 0.03771 seconds