I am trying to write a function which allows me to select a certain field from a table and return the sum of that field. What I came up with looks like this. As you can imagine, it does not work. :) Otherwise I would not write this.

The compiler claims that:
column cprmp300.##field## does not exist.

Does anyone have an idea on how I can solve this?


function extern domain cpcom.quan zsrmp.dll0001.get.master.plan(domain cpcom.plnc i.plnc fixed,
domain cpcom.plvl i.plvl,
domain cpitem i.plni fixed,
domain cpcom.sern i.pern,
domain tcmcs.chan i.chan fixed,
domain ttadv.fdnm i.field)
{
domain cpcom.quan quantity

select cprmp300._index1, cprmp300.##i.field##
from cprmp300
where cprmp300._index2 inrange {:i.plnc,:i.plvl,:i.plni,i:chan,59}
and {:i.plnc,:i.plvl,:i.plni,i:chan,65}
order by cprmp300._index2
selectdo
quantity = quantity + cprmp300.##i.field##
endselect
return(quantity)
}

This will not work for sure. I would suggest you to use dynamic SQL (sql.parse , sql.exec and sql.fetch functions) . Alternatively u can try db function db.eq , db.gt etc to accomplish this . Defiinitely embedded SQL like this will not help u.

You may also wish to use expression functions (expr.*) .

Hej,

I have a solution using dynamic sql, but thx. Out of pure curiosity, is there a way to do it without using dynamic sql?

U can do following.


function extern domain cpcom.quan zsrmp.dll0001.get.master.plan(domain cpcom.plnc i.plnc fixed,
domain cpcom.plvl i.plvl,
domain cpitem i.plni fixed,
domain cpcom.sern i.pern,
domain tcmcs.chan i.chan fixed,
domain ttadv.fdnm i.field)
{
long ret_expr
domain cpcom.quan quantity

ret_expr = expr.compile("cprmp300." & i.field)

select cprmp300.*
from cprmp300
where cprmp300._index2 inrange {:i.plnc,:i.plvl,:i.plni,i:chan,59}
and {:i.plnc,:i.plvl,:i.plni,i:chan,65}
order by cprmp300._index2
selectdo
|quantity = quantity + cprmp300.##i.field##
quantity = quantity + d.expr(ret_expr)
selecteos
expr.free(ret_expr)
endselect
return(quantity)
}

Thx. Looks like a nice solution. I will give it a try.