Home » Developer & Programmer » Reports & Discoverer » Z SHAPE TOTAL
| Z SHAPE TOTAL [message #440841] |
Wed, 27 January 2010 05:55  |
deepak3arora
Messages: 32
Registered: October 2009
Location: chandigarh
|
Member |
|
|
how can i calculate this thing :
openin_bal as 2000...
txn 1 as 200
txn 2 as 500
txn 3 as -300
like
2500 200 2700
2700 500 3200
3200 -300 2900
I have 2 database fields : one op_bal and multiple txn
Regards,
Deepak Arora
|
|
|
|
|
|
| Re: Z SHAPE TOTAL [message #441267 is a reply to message #440911] |
Sat, 30 January 2010 00:55  |
deepak3arora
Messages: 32
Registered: October 2009
Location: chandigarh
|
Member |
|
|
I DIDN'T FOUND ANYTHING IN FORUM REGARDING 2 THIS....
I WANT THIS 2 HAPPEN IN ORACLE REPORTS OR EITHER IN SQL QUERY...
I AM HAVING 2 TABLES (1)"OPENING_BAL" WITH OPENING BALANCES OF EACH PARTY_CODE AND
(2) "TRANSACTIONS" HAVING TRANSACTIONS OF EACH PARTY_CODE
AND I HAVE ALREADY PROVIDED THE TEST CASE
|
|
|
|
| Re: Z SHAPE TOTAL [message #441304 is a reply to message #441267] |
Sat, 30 January 2010 08:24 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
deepak3arora wrote on Sat, 30 January 2010 07:55AND I HAVE ALREADY PROVIDED THE TEST CASE
You did? Where? Someone has told you lies about how a test case is supposed to look like. It helps other people to easily create working environment so that they could try to help you. Because, as far as I'm concerned, I can live happily without your (or anyone else's) problems. So, if you need help, why don't you help us to help you?
This is how a test case should look like; try to remember and do it by yourself next time. It contains CREATE TABLE and INSERT INTO statements; if you did it, I wouldn't have to do it myself. Because, who can guarantee that I correctly understood what you were saying?
Besides, I believe that you did it wrong; if opening balance equals 2000, how come the result starts with 2500?
SQL> create table op_bal (party_code varchar2(1), op_bal number);
Table created.
SQL> create table txn (party_code varchar2(1), id number, val number);
Table created.
SQL> insert all
2 into op_bal values ('A', 2500)
3 into op_bal values ('B', 1000)
4 into txn values ('A', 1, 200)
5 into txn values ('A', 2, 500)
6 into txn values ('A', 3, -300)
7 into txn values ('B', 1, 100)
8 into txn values ('B', 2, -700)
9 select * from dual;
7 rows created.
SQL> select * from op_bal;
P OP_BAL
- ----------
A 2500
B 1000
SQL> select * from txn order by party_code, id;
P ID VAL
- ---------- ----------
A 1 200
A 2 500
A 3 -300
B 1 100
B 2 -700
SQL>
Here's one way to do that; I'm not saying it is perfect and it probably could be done better, but OK, here you go (let's call it <the_query>, for future reference):
SQL> select y.party_code,
2 y.id,
3 lag(y.run_tot) over (partition by y.party_code order by y.id) prev_tot,
4 y.val,
5 y.val + lag(y.run_tot) over (partition by y.party_code order by y.id) total
6 from
7 (select x.party_code,
8 x.id,
9 x.val,
10 sum(x.val) over (partition by x.party_code order by x.id rows unbounded preceding) run_tot
11 from
12 (select 0 id, o.party_code, o.op_bal val from op_bal o
13 union
14 select t.id, t.party_code, t.val from txn t
15 ) x
16 order by x.party_code, x.id
17 ) y
18 order by y.party_code, y.id;
P ID PREV_TOT VAL TOTAL
- ---------- ---------- ---------- ----------
A 0 2500
A 1 2500 200 2700
A 2 2700 500 3200
A 3 3200 -300 2900
B 0 1000
B 1 1000 100 1100
B 2 1100 -700 400
7 rows selected.
SQL>
How it works? It begins with the UNION of two tables, which emulates a situation where the opening balance represents a transaction (its ID would then be 0):SQL> select x.party_code, x.id, x.val
2 from (select 0 id, o.party_code, o.op_bal val
3 from op_bal o
4 union
5 select t.id, t.party_code, t.val
6 from txn t
7 ) x
8 order by x.party_code, x.id;
P ID VAL
- ---------- ----------
A 0 2500
A 1 200
A 2 500
A 3 -300
B 0 1000
B 1 100
B 2 -700
7 rows selected.
SQL>
Next, you calculate a running total. It can be done with the analytical form of the SUM function:SQL> select x.party_code,
2 x.id,
3 x.val,
4 sum(x.val) over (partition by x.party_code order by x.id rows unbounded preceding) run_tot
5 from
6 (select 0 id, o.party_code, o.op_bal val from op_bal o
7 union
8 select t.id, t.party_code, t.val from txn t
9 ) x
10 order by x.party_code, x.id;
P ID VAL RUN_TOT
- ---------- ---------- ----------
A 0 2500 2500
A 1 200 2700
A 2 500 3200
A 3 -300 2900
B 0 1000 1000
B 1 100 1100
B 2 -700 400
7 rows selected.
SQL>
Finally, you format the output by some help of another analytical function, LAG. The result is <the_query>.
Output contains rows with ID = 0. Omit them in report (in Format Trigger, "RETURN (ID <> 0);", or wrap the query with another SELECT, such as
select party_code, id, prev_tot, val, total
from <the_query>
where id <> 0
order by 1, 2;
That should be all, I guess.
|
|
|
|
| Re: Z SHAPE TOTAL [message #442124 is a reply to message #440841] |
Thu, 04 February 2010 18:13 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Great job by little foot.
As this question is raised in reports forum and the user is aslo new in this forum so far. So, i want to give user a thinking parameter that the same result can be achieved in oracle reports with using column summary etc ( and not using analytical function in query)
Good luck
-Dude
|
|
|
|
Goto Forum:
Current Time: Wed Nov 27 06:27:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
