Re: How to create the procedure???

Sorry, I find your solution a little bit too complex. What do you think about this request ?

select date, acc_no, sum(decode(DC,'D',amt,0)) D_amt, sum(decode(DC,'C',amt,0)) C_amt
from accd
where date between start_date and end_date group by date, acc_no
order date

More easy, isn't it ?

mashy_at_null.net wrote in article <6qsfk0$ou5$1_at_nnrp1.dejanews.com>...
> Hey there !!
>
> cStartDate and cEndDate will form the two parameters to the procedure and
the
> 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 Thu Aug 13 1998 - 09:42:58 CEST