Re: Help w/ algoritm to order table
Joao Natal wrote:
>
> Hi there,
>
> I'm presently working on an accounting software, and came across the
> following problem:
>
> Among other, the database has an "accounts" table and a "movements"
> table. The structure is as follows:
>
> ACCOUNTS MOVEMENTS
> code char(10) acctcode char(10)
> type char amount dec(10,2)
>
> Some sample data, could be:
>
> ACCOUNTS MOVEMENTS
> code type acctcode amount
> 1 T 111 1500
> 11 T 111 3000
> 111 M 111 -1500
> 112 M 12111 3000
> 12 T 12111 4000
> 121 T
> 12111 M
> 12112 M
> Acct Total
> 111 4000
> 112 0
> 11 4000
> 12111 7000
> 12112 0
> 121 7000
> 12 7000
> 1 11000
>
> Any ideas would be appreciated.
>
> TIA
>
> JN
Here's one way to do it:
#Select all the normal accts into a temp table
#And convert to integer for order by
create temp table tmp_acct_tbl
(acctcode char(10), acctno integer, amount dec(10,2))
with no log
insert into tmp_acct_tbl
select acctcode,0,amount from acct_table
where type="M"
update tmp_acct_tbl
set acctno=acctcode
#Declare the main cursor
declare acct_cur cursor for
select * from tmp_acct_tbl
order by acctno
#Declare a lookup cursor for the totalling accounts
let lkup_str=
"select acctcode, amount ",
"from acct_table ",
"where acctcode=? ",
"and type='T'"
prepare lkup_id from lkup_str
declare lkup_cur cursor for lkup_id
#Initialize an array for totalling accts
initialize old_t_acct[1].* to null
for i=max_t_acct_len-1 to 1 step -1
let old_t_acct[i]=old_t_acct[1]
end for
#Begin main loop
foreach acct_cur into acct_rec.*
#See if any total acctcodes have changed
#and send them to report
for i=length(acct_rec.acctcode)-1 to 1 step -1
if acct_rec.acctcode[1,i]=old_t_acct[i] then
exit for
else
open lkup_cur using acct_rec.acctcode[1,i]
fetch lkup_cur into t_acct_rec.*
let q_status=status
close lkup_cur
if q_status then else
output to report rpt1(t_acct_rec.*)
end if
let old_t_acct[i]=acct_rec.acctcode[1,i]
end if
end for
#Now send regular acct to report
output to report rpt1(acct_rec.acctcode,acct_rec.amount)
end foreach
#Send last bunch of totalling accts
for i=length(acct_rec.acctcode)-1 to 1 step -1
#Lookup acct as above and
#if found output to report
end for
hope this helps, this is the first thing i've tried to
post so please forgive me that i've not tested posting news
yet.
*** Disclaimer: These are the opinions of the poster not Amgen Inc.***