#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Variable substitution


    hi,

    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


    and then

    Code:
    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:


    Code:
    -- 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?


    Thank you.
    Last edited by nagysz; November 24th, 2011 at 04:23 AM.

IMN logo majestic logo threadwatch logo seochat tools logo