At 07:00 PM 12/6/2004, Namit Nathwani wrote:

>Could you kindly give me a small eg. for a stored procedure returning a value.

Errr, there are tons of example of stored procedures
that return results in the documentation, in responses
on this list, etc.

If you're looking for the algorithm that the stored
procedure should use to compute your results, that's
a slightly harder problem. If I understand, you're
retrieving records for one account and want a report
showing credits applied to earlier debits. My first
cut at that would be to create a table where you can
store the records for the account, identify the credits
and the debits that are outstanding at the time the
credit is identified, then apply the credits to the
debits.

Which brings us to the area of temporary tables. You'll
need a table definition that includes a slot for each
value you want to return, plus the account number, plus
a value you'll get from a generator. The account number
and generator value will keep you from accidentally
including results from concurrent users of the procedure.
Other inputs for the table come from your original SQL.

insert into report_1_temp_table (gen_val, iacctid, iid,ino,
ddt, bdramt, bcramt)
SELECT :gen_val, :acct, t.iID, t.iNo, t.dDt,
s.bDrAmt, s.bCrAmt
FROM tAccountsJV t join sAccountsJV s
ON t.iID = s.iPID
WHERE s.iAcctID = :acct ;

Once you've done the computation that distributes the
credits to the debits, start a FOR SELECT loop with
a suspend to return the results. At the end of the
procedure delete the rows you stored.

DELETE FROM report_1_temp_table
WHERE gen_val = :gen_val and iacctid = :acct;

Regards,


Ann