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 |
|
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
|
|
|
Re: Missing Record in running Total [message #655935 is a reply to message #655934] |
Sat, 17 September 2016 06:51 |
Solomon Yakobson
Messages: 3273 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 #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 #655940 is a reply to message #655939] |
Sat, 17 September 2016 09:07 |
Solomon Yakobson
Messages: 3273 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 #655942 is a reply to message #655940] |
Sat, 17 September 2016 09:51 |
Solomon Yakobson
Messages: 3273 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 |
|
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
|
|
|
|
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
|
|
|
Re: Missing Record in running Total [message #655955 is a reply to message #655946] |
Sun, 18 September 2016 12:09 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
bluetooth420 wrote on Sat, 17 September 2016 20:47BAL 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)
|
|
|
|
|
|
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;
|
|
|
Re: Missing Record in running Total [message #655980 is a reply to message #655978] |
Mon, 19 September 2016 07:22 |
cookiemonster
Messages: 13923 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 #655986 is a reply to message #655983] |
Mon, 19 September 2016 08:36 |
cookiemonster
Messages: 13923 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 #655989 is a reply to message #655988] |
Mon, 19 September 2016 09:43 |
cookiemonster
Messages: 13923 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
bluetooth420 wrote on Mon, 19 September 2016 15:13Dear 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 |
|
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)
|
|
|
Goto Forum:
Current Time: Sun May 19 08:49:58 CDT 2024
|