Re: How to create the procedure???

From: <mashy_at_null.net>
Date: Wed, 12 Aug 1998 16:24:32 GMT
Message-ID: <6qsfk0$ou5$1_at_nnrp1.dejanews.com>

[Quoted] Hey there !!

cStartDate and cEndDate will form the two parameters to the procedure and the [Quoted] sql will output the desired result ...

This Query should solve ur problem :

---

__________Start of the SQL____________
Select distinct

       mash_a.period_id,
       mash_a.acc_no,
       NVL(test_a.D_Amt,0) D_Amt,
       NVL(test_b.C_Amt,0) C_Amt
from   mashy_tab   mash_a,
       ( select period_id,
                acc_no,
                sum(amount) D_Amt
         from   mashy_tab
         where  acc_type = 'D'
         group  by period_id, acc_no
       ) test_a,
       ( select period_id,
                acc_no,
                sum(amount) C_Amt
         from   mashy_tab
         where  acc_type = 'C'
         group  by period_id, acc_no
       ) test_b
where  mash_a.period_id = test_a.period_id (+)
and    mash_a.acc_no    = test_a.acc_no (+)
and    test_b.period_id (+) = mash_a.period_id

and test_b.acc_no (+) = mash_a.acc_no and mash_a.period_id between cStartDate

                        and     cEndDate

order by 1, 2 ;

_________End of the SQL__________________

Hope i have got ur problem right !!

Regards ,

Mashya !

In article <35cfb65b.11722580_at_news.twsc.pouchen.com.tw>,   violin.hsiao_at_mail.pouchen.com.tw (Violin) wrote:
> Hello,
> I have a table named "ACCD" and data:
> DATE ACC_NO DC AMT
> ----------- --------------- ----- ---------
> 961004 111101 D 1540
> 961003 111105 D 3068
> 961004 111101 C 992
> 961004 111106 D 5300
> 961002 111101 D 935
> 961007 111104 D 7752
> 961009 111101 D 2172
> 961003 111102 C 8493
> 961008 111101 C 318
> 961007 111104 C 468
> 961006 111101 C 660
> 961005 111103 C 995
> 961009 111102 D 665
> 961008 111102 C 25949

>

> I want to create a procedure with pass in
> start-of-date and end-of-date,
> then response sort by DATE,group by ACC_NO,each ACC_NO counts
> sum(D_AMT) where DC='D',sum(C_AMT) where DC='C'

>

> like this:
> DATE ACC_NO D_ AMT C_AMT
> ----------- --------------- ------------- -----------
> 961002 111101 935 0
> 961003 111102 0 8493
> 961003 111105 3068 0
> 961004 111101 1540 992
> 961004 111106 5300 0
> 961005 111103 0 995
> 961006 111101 0 660
> 961007 111104 7752 468
> ::::::::::::

>

> REALLY NEED HELP!!
> How could I do with this???
> Please give me some suggestion,Thank you billions!
> Please Cc to : violin.hsiao_at_mail.pouchen.com.tw,Thank you :)
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Aug 12 1998 - 18:24:32 CEST