Re: Dynamic column names in UPDATE
In article 6np@iliana.csl.sni.be, evdh@csl.sni.be (Eric Van der Hulst) writes: > 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 I don't think that you can have variable column names. But you can acheive that by making a small modification to your program: let stringUpdate = "update plan_compt", "set cumdeban = cumdeban + ? ", "set cumcrean = cumcrean + ? " CASE MONTH(ecriture.periodcp) when 1 let stringUpdate = stringUpdate, "creditjan = creditjan + ? " , " debitjan = debitjan + ? " when 2 let stringUpdate = stringUpdate, "creditfeb = creditfeb + ? ", "debitfeb = debitfeb + ? " .. .. END CASE then do your prepare and execute statements. --shyam