Expert: Jean-Pierre Zuate - 1/26/2006

Question
I'm writing an UPDATE statement where I must include the name of the column which I want to update in a host variable. That works in the left part of the SET clause, but in the right, the program 'reads' the character chain (the name of the column), not its value, as I want.

UPDATE :arr_tablas.tabla                      
SET :w_ref_col= :w_ref_col + :w_maximo
WHERE cod= :w_max_cod  

w_ref_col is a varchar variable and the column is  integer.

Can you give me one idea about this? Thanks.  

Answer
Hello,

I suppose your SQL is in a C programm (embeded SQL) or ABF. If you look in the R3 doc PDF files and search for "sql prepare" string, you'll find what you want, I think.

Also look at sql prepare, describe and so on in the doc. Those SQL order will permit you to execute a query formatted in a variable, so both value and column name can be dynamic.

The variable content is the query Ingres will execute and should look like this :

UPDATE table_name                
SET column_name = column_name + value
WHERE cod= value

where table_name, column_name and value
are provided by the variables
:arr_tablas.tabla, :w_ref_col, :w_maximo and :w_max_cod.

Hope this clear, and hope this help you. Let me know if not.

Cheers,
Jean-Pierre