building view help

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

thanks tabreaz

I am trying your query hope it will be ok

Regards

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

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
/

---

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.

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

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
/

---

Thanks very much tabraez ,

the query works,

but the prevbal & accbalance shown 0 values !!

my regards to you and thanks for your help

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

---

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