building view help

Printable View

09-07-2006, 04:34 AM
hsbdba

building view help

Hi,

I have table having the following

acc_name , date , debit ,credit

i need to build view as the following

acc_name , date , prev_balance,debit,credit,acc_balance

looking for your help

regards
09-07-2006, 04:42 AM
hsbdba

i have to do that urgent pls help me
09-07-2006, 05:29 AM
jvalerof

Hi,

I don't understand exactly what's the problem?
For example, what is the meaning of prev_balance and acc_balance?
09-07-2006, 06:06 AM
hsbdba

Hi jvalerof,

thanks for your reply

prev_balance = previous balance before the accounting date
acc_balance = accumulative balance

hope it is clear
09-07-2006, 06:13 AM
Scorby

If you want to know what the syntax of a "create view" statement is, then you ought to know that and I don't think anyone on this forum will help you with anything so basic.

If you have a specific problem with creating the view e.g. there is some complex processing you want to do in it, then we may be able to help you. You will need to give more details on what the view is doing and why you are having a problem creating it.
09-07-2006, 06:15 AM
hsbdba

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

this example of what i look for in my view

regards for all
09-07-2006, 06:32 AM
jvalerof

Hi hsdbda,

for what I see:

acc_balance = prev_bal + debit - credit

is that right?

But, how do you calculate prev_bal?
09-07-2006, 06:32 AM
Scorby

The question cannot be answered on the basis of the information you have given. We will need to know:

a) what tables the view based on and how they join together
b) which columns in the table(s) are required in the view and whether they are named differently in the view
c) any special processing that needs to take place
09-07-2006, 06:51 AM
hsbdba

very simple

one table same fields as mentioned above

reagrds
09-07-2006, 07:08 AM
hsbdba

where are the experts

is it too cmplicated
09-07-2006, 07:32 AM
jvalerof

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:
09-07-2006, 09:09 AM
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.
09-07-2006, 09:44 AM
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
09-07-2006, 10:07 AM
slimdave

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.
09-07-2006, 10:35 AM
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.
09-07-2006, 10:36 AM
tabreaz

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
09-07-2006, 10:50 AM
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
09-07-2006, 11:21 AM
tabreaz

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?
09-08-2006, 11:00 AM
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.
09-08-2006, 04:03 PM
slimdave

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
09-09-2006, 02:20 AM
hsbdba

Quote:

Originally Posted by jvalerof

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:

Any way thanks for your update

But You Must Know how to inquery about any thread update if your mind is soo small to understand from table 4 fields how to create 6 or 10 fields my advise to you is not to concern about such threads

bye
09-09-2006, 02:32 AM
hsbdba

thanks tabreaz

I am trying your query hope it will be ok

Regards
09-09-2006, 02:50 AM
hsbdba

Hi Tabreaz

but the result is not correct

my aim is how to calculate the previous ablance of the date

so the view will be having the follwoing

AccName, Date,Prev_Bal,debit,Credit,AccBalance

accname,date,debit,credit are there in the table

regards
09-09-2006, 03:53 AM
tabreaz

This query produces the accurate result but u have to work around to get previous balance.

Code:

SELECT accname, accdate, debit, credit, SUM(debit) OVER(PARTITION BY accname ORDER BY accname, accdate) - SUM(credit) OVER(PARTITION BY accname ORDER BY accname, accdate) accbalance FROM accs ORDER BY accname, accdate /
09-09-2006, 04:16 AM
tabreaz

This query will compute both previous balance and account balance

Code:

SELECT accname, accdate, LAG(accbalance, 1, NULL) OVER(PARTITION BY accname ORDER BY accname, accdate) prevbal, debit, credit, balance FROM (SELECT accname, accdate, debit, credit, SUM(debit) OVER(PARTITION BY accname ORDER BY accname, accdate) - SUM(credit) OVER(PARTITION BY accname ORDER BY accname, accdate) accbalance FROM accs ORDER BY accname, accdate) ORDER BY accname, accdate /

Waiting for your reply if any error comes.
09-09-2006, 05:44 AM
hsbdba

Thanks tabreaz,

i want to remind you that i don't have fields for previous balance nither accumlative one.
just i have date,debit,credit.
--------------
for the previous query it shows error on "balance" shown it is invalid column name

my regards
09-09-2006, 05:55 AM
tabreaz

Yea the query uses only "Accountname, date,debit,credit"

HTML Code:

for the previous query it shows error on "balance" shown it is invalid column name

Actually I used balance where I use alias in Query as AccBalanace.

Try this query, it should work..

Code:

SELECT accname, accdate, LAG(accbalance, 1, NULL) OVER(PARTITION BY accname ORDER BY accname, accdate) prevbal, debit, credit, accbalance FROM (SELECT accname, accdate, debit, credit, SUM(debit) OVER(PARTITION BY accname ORDER BY accname, accdate) - SUM(credit) OVER(PARTITION BY accname ORDER BY accname, accdate) accbalance FROM accs ORDER BY accname, accdate) ORDER BY accname, accdate /
09-09-2006, 06:12 AM
hsbdba

Thanks very much tabraez ,

the query works,

but the prevbal & accbalance shown 0 values !!

my regards to you and thanks for your help
09-09-2006, 06:24 AM
tabreaz

create table accs
(accname varchar2(10),
accdate date,
debit number,
credit number);

SQL> insert into accs values('XYZ', sysdate-10, 2000, 0);

1 row created.

SQL> insert into accs values('XYZ', sysdate-09, 1000, 0);

1 row created.

SQL> insert into accs values('XYZ', sysdate-08, 1200, 0);

1 row created.

SQL> insert into accs values('XYZ', sysdate-07, 0, 1200);

1 row created.

SQL> insert into accs values('XYZ', sysdate-05, 0, 1600);

1 row created.

SQL> insert into accs values('XYZ', sysdate-02, 1000, 0);

1 row created.

SQL> insert into accs values('ABC', sysdate-07, 0, 1200);

1 row created.

SQL> insert into accs values('ABC', sysdate-06, 0, 1200);

1 row created.

SQL> insert into accs values('ABC', sysdate-05, 5000, 1200);

1 row created.

After running the query I got following results, which I guess are correct results.

HTML Code:

ACCNAME ACCDATE PREVBAL DEBIT CREDIT ACCBALANCE ---------- --------- ---------- ---------- ---------- ---------- ABC 03-MAY-06 0 1200 -1200 ABC 04-MAY-06 -1200 0 1200 -2400 ABC 05-MAY-06 -2400 5000 1200 1400 XYZ 30-APR-06 2000 0 2000 XYZ 01-MAY-06 2000 1000 0 3000 XYZ 02-MAY-06 3000 1200 0 4200 XYZ 03-MAY-06 4200 0 1200 3000 XYZ 05-MAY-06 3000 0 1600 1400 XYZ 08-MAY-06 1400 1000 0 2400
09-09-2006, 06:53 AM
hsbdba

Hi Tabreaz

I try your example and it comes 100% correct so i have to back why it is shown 0 for the data in my table
09-10-2006, 08:39 PM
gamyers

Deleted by author
09-11-2006, 01:31 AM
tabreaz

HTML Code:

Deleted by author

What you mean by that?
09-11-2006, 08:37 AM
slimdave

I can't believe that this thread is still active
09-20-2006, 07:43 AM
ahmadbilal

create funtions for managing
acc_balance = prev_bal + debit - credit
and use it in this view