| self joins (cr and dr totals) [message #370861] |
Fri, 25 February 2000 00:48  |
alkakaneja
Messages: 10
Registered: February 2000
|
Junior Member |
|
|
hi,
I have a table (transaction table of receipt master) that contains rec_no,date,adjustment no and adjustment amount and one flage describing cr or dr. I want to select sum of amount of cr flag and dr flag separately, the problm is that the number on which the sum is required exists in credit side only.
(i.e. i have credit adjustment number but no rec_no and i have to sum the adjustment amount of that adjustment number (flag cr) and sum of adjustment amount of the same rec_no (flag dr)).
i am able to do this with pl/sql procedure, but is there any way to do it with one sql statement.
thanks in advance
alka
|
|
|
|
| Re: self joins (cr and dr totals) [message #370862 is a reply to message #370861] |
Fri, 25 February 2000 06:22  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo Alka,
I had first some problem understanding what you want to do.
I have made an example on how I think it must be.
When this is not correct, please post a correct example.
INPUT:
******
rec_no adjustment_no adjustment_amount flag
1 10 cr
1 20 cr
11 1 100 dr
11 1 200 dr
100 1000 cr
100 2000 cr
1100 100 10000 dr
1100 100 20000 dr
OUTPUT
******
REC_NO ADJUSTMENT_NO CR_AMO DR_AMO
11 1 30 300
1100 100 3000 30000
SELECT
MAX(rec_no) rec_no
,adjustment_no
,SUM(DECODE(flag,'CR',adjustment_amount,NULL)) cr_amo
,SUM(DECODE(flag,'DR',adjustment_amount,NULL)) dr_amo
FROM transaction
GROUP BY adjustment_no
;
Hope this helps
Greetings,
Thierry.
|
|
|
|
| Re: self joins (cr and dr totals) [message #370864 is a reply to message #370862] |
Mon, 28 February 2000 02:22 |
alkakaneja
Messages: 10
Registered: February 2000
|
Junior Member |
|
|
thanks Thierry, but my input is a bit different
it is something like:
rec_no adjustment_no adj.amount flag
11 999 1000 cr
11 001 500 dr
11 002 500 dr
20 998 5000 cr
20 005 4000 dr
i am querrying on adjustment number (rec_no is not known)-
the output should be
rec_no adj_number(cr) cr_amount dr_amount
11 999 1000 1000
20 998 5000 4000
...
hope i am clear this time,
thanks in advance,
alka
(sorry, the other reply is wrong)!
|
|
|
|
| Re: self joins (cr and dr totals) [message #370866 is a reply to message #370862] |
Mon, 28 February 2000 11:09 |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo Alka,
Try following sql :
SELECT rec_no
,AVG(DECODE(flag,'CR',adjustment_no,NULL)) adj_no
,SUM(DECODE(flag,'CR',adjustment_amount,0)) cr_amount
,SUM(DECODE(flag,'DR',adjustment_amount,0)) dr_amount
FROM transaction tr
GROUP BY rec_no
;
This must be it. (When not then reply).
Greetings,
Thierry.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
