November 24th, 2011, 04:16 AM
i'll write an example to demonstrate what i want, and then i'll ask a question
there in an SP with output fields o1, o2, ... o15
i return an excel like table with this SP, the number of colums is unknown (but in the SP i limit it to 15)
there will be numbers in these cells, and the last column will be a TOTAL column
in the SP i calculate the ROWTOTAL variable
if (max_col=1) then c2=rowtotal
else if (max_col=2) then c3=rowtotal
else if (max_col)=14 then c15=rowtotal
(max_col is the number of effective columns...the rest till 15 will not be used at listing)
and here's the question:
i'd like to replace this long if-else block with something more elegant
i thought of something like this:
-- say max_col=6, and rowtotal=123456
a=max_col+1; -- then a=7
totalcol = 'o' || cast(a as varchar(3); -- then totalcol='o7'
sql='totalcol =' || cast(rowtotal as varchar(20)) -- so sql = 'o7 = 123456';
execute statement sql;
this looks simple, but is not correct, since execute statement can only execute SQL statements like SELECT or EXECUTE PROCEDURE.
How should i do it correctly?
Last edited by nagysz; November 24th, 2011 at 04:23 AM.