OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL/SQL [message #10086]

Fri, 02 January 2004 10:25

Vasan
Messages: 4
Registered: July 2001

Junior Member

There is a table table with 4 columns .

ACCT_NMBR TRANS_DATE TRANS_AMT TRANS_TYPE
---------- ----------- ---------- ----------
1 20-MAR-2003 300 DEBIT
1 20-MAR-2003 100 DEBIT
1 20-APR-2003 500 CREDIT
1 20-APR-2003 400 CREDIT
1 20-MAY-2003 700 CREDIT
1 20-MAY-2003 250 DEBIT

2 20-MAR-2003 300 DEBIT
2 20-MAR-2003 100 DEBIT
2 20-APR-2003 500 CREDIT
2 20-APR-2003 400 CREDIT
2 20-MAY-2003 700 CREDIT
2 20-MAY-2003 250 DEBIT

3 20-MAR-2003 300 DEBIT
3 20-MAR-2003 100 DEBIT
3 20-APR-2003 500 CREDIT
3 20-APR-2003 400 CREDIT
3 20-MAY-2003 700 CREDIT
3 20-MAY-2003 250 DEBIT

How to create a cursor that summarizes and displays all transactions between two givendates
in the following format
(which I want to insert straight away into another table with out using Variables)
(IF it is DEBIT then transamount should be in MONEY_IN
ELSE if it is credit then transamount iis MONEY_OUT)

ACCT_NMBR TRANS_DATE MONEY_IN MONEY_OUT
1 20-MAR-2003 400 0
1 20-APR-2003 0 900
1 20-MAY-2003 250 700

2 20-MAR-2003 400 0
2 20-APR-2003 0 900
2 20-MAY-2003 250 700

3 20-MAR-2003 400 0
3 20-APR-2003 0 900
3 20-MAY-2003 250 700

Thanks

Report message to a moderator

Re: PL/SQL [message #10089 is a reply to message #10086]

Fri, 02 January 2004 21:52

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use decode to create 2 pseudocolumns:

SQL> insert into new_table
  2  ( acct_nmbr
  3  , trans_date
  4  , amount_in
  5  , amount_out
  6  )
  7  (select acct_nmbr
  8   ,      trans_date
  9   ,      sum(decode(trans_type, 'DEBET', trans_amt, 0)) amount_in
 10   ,      sum(decode(trans_type, 'DEBET', 0, trans_amt)) amount_out
 11   from   old_table
 12   group  by acct_nmbr
 13   ,      trans_date
 14  )
 15  /

9 rows inserted

SQL> select * from new_table
  2  /

ACCT_NMBR TRANS_DATE  AMOUNT_IN AMOUNT_OUT
--------- ----------- --------- ----------
        1 20-03-2003        400          0
        1 20-04-2003          0        900
        1 20-05-2003        250        700
        2 20-03-2003        400          0
        2 20-04-2003          0        900
        2 20-05-2003        250        700
        3 20-03-2003        400          0
        3 20-04-2003          0        900
        3 20-05-2003        250        700

hth
Frank

Report message to a moderator

Previous Topic:	static Vs dynamic sql
Next Topic:	sybase books?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:19:48 CDT 2024