building view help

Quote:

---

Originally Posted by hsbdba

where are the experts

is it too cmplicated

---

Perhaps, you must learn to explain properly your problems before post them!

Do you think a post telling "I've had a four column table and I need a 6 column view. What must I do?" is explaining anything to anyone except you? :confused:

You need analytic functions to do this ... have a search here and at http://asktom.oracle.com for examples of LAG and SUM using the analytic syntax. A bit of a learning curve but it will do exactly what you want.

HTML Code:

---


acc_name date prev_bal debit credit acc_balance
XZ 01/01/06 1000 100 0 1100
02/01/06 1100 0 1000 100

---

What I understand from the data you provided, u need the query like below to compute account balance.

Code:

---


select acc_name, date,
prev_bal, debit, credit,
decode(debit, 0, prev_bal-credit,
        prev_bal+debit) acc_bal
from tab

---

Quote:

---

Originally Posted by tabreaz

HTML Code:

---


acc_name date prev_bal debit credit acc_balance
XZ 01/01/06 1000 100 0 1100
02/01/06 1100 0 1000 100

---

What I understand from the data you provided, u need the query like below to compute account balance.

Code:

---


select acc_name, date,
prev_bal, debit, credit,
decode(debit, 0, prev_bal-credit,
        prev_bal+debit) acc_bal
from tab

---

---

No, the previous balance is in a different record, and the cumulative needs to be calculated based on all previous records.

I think the query must be similar to

select b.acc_name, b.date,sum(a.debit)-sum(a.credit) prev_bal, b.debit,b.credit,sum(a.debit)-sum(a.credit)+b.debit-b.credit acc_bal
from tab a, tab b
where a.acc_name=b.acc_name and
a.date < b.date
group by b.acc_name, b.date, b.debit, b.credit

but I don't know exactly how prev_balance is calculated.

Hope that helps.

Thanks dave for correcting


I think this query solves the problem

Code:

---

SELECT acc_name, date,
        balance,
        credit,
        debit
        SUM(bal) over(ORDER BY acc_name, date) + debit - credit AccountBalance
FROm tab       
ORDER BY acc_name, date

---

You probably need the PARTITION clause, if you want cumulative total by account, otherwise you can skip it. If you just want to sum the prior values for any account/date then you need to include the RANGE clause.

Have a look here for a very similar example: http://download-west.oracle.com/docs...163.htm#i89126

HTML Code:

---


Dave

You probably need the PARTITION clause, if you want cumulative total by account, otherwise you can skip it. If you just want to sum the prior values for any account/date then you need to include the RANGE clause.

Have a look here for a very similar example: [url]http://download-west.oracle.com/doc...s163.htm#i89126[/url]

---

What is the usage of "RANGE UNBOUNDED PRECEDING" in the query from the link?

HSBDBA Did u got the query?

HTML Code:

---


What is the usage of "RANGE UNBOUNDED PRECEDING" in the query from the link?

---

Wrong phrase, I mean what is the purpose of "RANGE UNBOUNDED PRECEDING", not usage.

Quote:

---

Originally Posted by tabreaz

HSBDBA Did u got the query?

HTML Code:

---


What is the usage of "RANGE UNBOUNDED PRECEDING" in the query from the link?

---

Wrong phrase, I mean what is the purpose of "RANGE UNBOUNDED PRECEDING", not usage.

---

http://download-west.oracle.com/docs...001.htm#i97640