[RBASE-L] - RE: SQL longshot!

[Rommel Relosa]

Atrix,     How about if you write a stored procedure say GetTransTypeTotal that will accept two arguments, AcctID and TranState? The stored procedure will return the result of this SELECT statement.         SELECT (SUM(AcctIDAmount))     FROM TranAcctID     WHERE AcctID = <passed AcctID>     AND TranState = <passed TranState>       With this stored procedure you can use this select:     SELECT AccountNo,AcctID,OrigApp, (CALL GetTransTypeTotal(AcctID,'A')) AS tmpA, (CALL GetTransTypeTotal(AcctID,'B')) AS tmpB, (CALL GetTransTypeTotal(AcctID,'C')) AS tmpC, (CALL GetTransTypeTotal(AcctID,'D')) AS tmpD, (OrigApp - (CALL GetTransTypeTotal(AcctID,'A')) - (CALL GetTransTypeTotal(AcctID,'B')) + (CALL GetTransTypeTotal(AcctID,'D'))) AS tmpBalance FROM Accounts WHERE <whatever clause you want>     I've used multiple CALLs on SELECT statements and the performance is quite responsive.  You have to make sure you have good indexes on AcctID and TranState.     This way also, if you add new TranStates, all you have to do is change the 2nd argument in the stored procedure call.   The only caveat though is that the SELECT statements tend to get really long really quick.  :)       Good luck,   Rommel   "There is always more than one way to skin a cat."   PS: Yes, I always alias "_expression_ columns" so the headers of the resultset will be more helpful.  :)           -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Atrix Wolfe Sent: Wednesday, July 30, 2003 3:26 PM To: RBASE-L Mailing List Subject: [RBASE-L] - SQL longshot! hey guys,   I have a problem im working on and was wondering if theres a way to do it more efficient than i am   I have 2 tables, One stores account numbers, their unique ID and their starting balance   Table Accounts: Accountno text 50 Acctid integer OrigApp currency   The other stores acctids, an amount for a transaction and a transaction type of A,B,C or D.   Table TranAcctid: Acctid integer AcctidAmount currency TranState text 1   The balance of an account = OrigApp - TranAcctid(A's) - TranAcctid(B's) + TranAcctid(D's)   so A and B are subtracted, C is ignored and D is added...   The way im doing it now is joining the accounts table with tranacctid and manualy looping through the results with witango and calculating the balances that way...then after that i have to go back to the database to get all the accounts that dont use transactions (arent in tranacctid) and display those.   kinda a pain and pretty darn slow!   is there a way to get the balance of all the accounts with a single sql statement?  Some kind of crazy join and group by or something?   Thanks for the help! Atrix