SQL Expressions and Parameters

[goodprg]

How can I use parameters in a sql expression?

(select SUM(column_value) from table1 where calender_year = ?parameter_value)

?How can this be done? When I look in the panes above I do not see the parameter listed.

Thanks.

 

[Turkbear]

Hi,
I am not sure you can..When the Sql expression is created, there is no value in the parameter for it to use and, anyway, the database is 'unaware' of the CR parameter.

IIRC, Sql expressions are usually limited to what you could use in a direct Sql query against the database.

 

[lbass]

If you are working in 8.0 or 8.5 (but possibly not higher versions), you could maybe fudge this by creating the SQL expression like this:

(select sum(AKA.`column_value`) from Table1 AKA where
{fn year(AKA.`date`)} = {fn year(Table1.`date`)})

This would create sums by year. You could then use the parameter to limit the year in the record selection formula so only the relevant SQL expression result would be returned.

In 9.0 and above, I think you would have to use the Add Command feature. I believe you have the option of building in parameters (at least in higher versions), but I can't really help you with that.

-LB