Passing table name in SQL from control

[rehara]

Hi All,

My search on this topic have not gone well so I hope someone here may have some insight.

I have highly partitioned data. The partition logic is off loaded to the clients for speed.

I'm attempting to use the tool chain iReport->JasperServer to interact with this data.

The data is bulk time series stuff.

I have successfully created a report with a control to gather the required table name from the user.

The Query:

select $P{parameter1} as series, value, dt from  lrw_1_speed where dt > now() - interval '1 hour';

Produces the expect results.

But what I want is:

select $P{parameter1} as series, value, dt from  $P{parameter1}  where dt > now() - interval '1 hour';

 

The following error is reported by the PostgreSQL server ( 8.2.4 ):

ERROR:  syntax error at or near "$2" at character 37
STATEMENT:  select $1 as series, value, dt from $2 where dt > now() - interval '1 hour';
 

This fails too:

select fred as series, value, dt from  $P{parameter1}  where dt > now() - interval '1 hour';

Suggestions?

 

Thanks.

[javi_123]

Try it with exclamation mark i.e. $P!{parameter1}

[rehara]

That did the trick. Thanks

Is that how you force a change from prepared statements?

Do you have a link in to the documentation for this?

Thanks again