building view help

[jvalerof]

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?

[slimdave]

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.

[tabreaz]

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.

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

[slimdave]

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.

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.

[jvalerof]

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.

[tabreaz]

Thanks dave for correcting


I think this query solves the problem

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

[slimdave]

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

[tabreaz]

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?

[tabreaz]

HSBDBA Did u got the query?

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.

[slimdave]

HSBDBA Did u got the query?

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