Re: Need help with SQL statement

On Wed, 7 Jan 1998, Jon Gjerset wrote: } I trying to summarize data from a table that has the following } structure: } } acctkey char(18) } gldtype char(1) } gldyear decimal(2,0) } actbbal decimal(17,2) } actb1 decimal(17,2) } [...actbX...] } actb12 decimal(17,2) } } These are account balances stored for each accounting period, } corresponding to the months of the year. Each actbX holds the account } balance for that period. } } I want to have a dynamic sql statement that will allow me to loop } through and sum all of the numbers in a certain actbX period based on a } given month. In otherwords, I want my statement to look something like } this: } } select sum(actbX) into acctbal from tablename } } The actbX needs to be dynamic. I need to be able to build a statement } that will have the period I want to summarize included in it. Correct. } Unfortunately, PREPARED statements won't work for this Incorrect. Prepared statements will work, but you have to use them slightly differently than you are currently thinking of doing. } because you cannot use the INTO variable clause with them. Correct observation, but incorrect conclusion. You write your SQL statement into a string with no INTO clause: SELECT SUM(actbX) FROM tablename You prepare a statement from the string. You declare a cursor for the statement. You fetch into the acctbal variable using the notation: FETCH cursorname INTO acctbal The exact syntax will depend on which language you are using (ESQL/C, I4GL, NewEra, ...), but the gist of the above is correct. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>