Re: Dynamic column names in UPDATE

->From: evdh@csl.sni.be (Eric Van der Hulst) ->Subject: Dynamic column names in UPDATE ->Date: 16 Mar 1994 15:23:33 GMT ->Reply-To: evdh@csl.sni.be (Eric Van der Hulst) ->Organization: Siemens Nixdorf -> ->Hello folks, -> -> My first mail into this interesting newsround! -> ->I am a beginner inj Informix 4GL and I am using a table ->with a funny contents, namely two numbers debit and credit ->for each month of a certain year. We wanted to have these ->numbers in one record. ( We use debitjan,creditjan ... ) ->Now when they are updated, I receivethe number of the month ->(1..12) and I have to covert this into the column name. ->I thought I found a fantastic way of doing this using the ->PREPARE and EXECUTE commands, but this doesn't work for ->variable names of columns, only for variable values for those ->columns. -> ->FUNCTION Foo ( ecriture ) -> -> DEFINE -> ecriture RECORD LIKE ecrit_analy.*, -> stringUpdate CHAR(500), -> monthName CHAR(3), -> debitCol CHAR(8), -> creditCol CHAR(8) -> -> CASE MONTH(ecriture.periodcp) -> WHEN 1 -> LET monthName = "jan" -> WHEN 2 -> LET monthName = "fev" ->... -> WHEN 12 -> LET monthName = "dec" -> END CASE -> -> LET debitCol = "debit",monthName -> LET creditCol = "credi",monthName -> LET stringUpdate= "UPDATE plan_compt ", -> "SET cumdeban=cumdeban + ? ,", -> " cumcrean=cumcrean + ? ,", -> " ? = ? + ? ,", -> " ? = ? + ? ", -> "WHERE ...condition -> PREPARE queryUpdate FROM stringUpdate -> EXECUTE queryUpdate -> USING ecriture.montdebi,ecriture.montcrdi, -> debitCol,debitCol,ecriture.montdebi, -> creditCol,creditCol,ecriture.montcrdi -> ->END FUNCTION -> ->I realise what I did was a bit crazy, but it could work if ->prepare was only a kind of parser. -> ->Does anybody know a way to reach this kind of dynamic ->access to tables ( variable column names ). -> -> Eric Van der Hulst -> Siemens-Nixdorf -> evdh@csl.sni.be -> Shyam Davuburu (shyam@robadome.com) has already posted one response to this inquiry. What Shyam said was correct, but I believe it can be improved upon. Eric, since you will not be using the prepared statement in a loop, I see no advantage to leaving any variables ("?") in the statement. Just create a complete statement by doing concatenations of strings and program variables, and then PREPARE and EXECUTE it. LET stringUpdate= "UPDATE plan_compt ", "SET cumdeban = cumdeban + ", ecriture.montdebi, ",", " cumcrean = cumcrean + ", ecriture.montcrdi, ",", " ", debitCol, " = ", debitCol, " + ", ecriture.montdebi, ",", " ", creditCol, " = ", creditCol, " + ", ecriture.montcrdi, " ", "WHERE ...condition" PREPARE queryUpdate FROM stringUpdate EXECUTE queryUpdate Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\