dynamic where clause condition

[antzjos]

Hi,

 

Is it possible to dynamically add the where clause to the query depending on the parameter passed.

 

I tried the following but did not work

 

select colmn1, colmn2 from table1

$P{CLAUSE1}

 

I defined a parameter "CLAUSE" as string type and "PARAM" as Integer and assigned the value

"where id="+$P{PARAM1} to the parameter "CLAUSE".

 

Thanks

[mikemaze]

Have you tried a case statement?

[antzjos]

havnt tried it for this scenario yet, I can try case statement.

 

thnx for the response.

[jmurray]

You need to use the exclamation mark to force inline insertion of a statement rather than a string.

 

 

Say CLAUSE contains the text where id='200BA'

 

 

select * from atable

$P{CLAUSE};

 

 

would then result in the database engine seeing this:

 

 

select * from atable "where id='200BA'";

 

 

 

Clearly wrong. But if you use the exclamation mark like this:

 

 

select * from atable

$P!{CLAUSE};

 

 

then the database engine will see this:

 

 

select * from atable where id='200BA';

 

 

which is much more betterer.

Post edited by: jmurray, at: 2007/02/14 02:39

[antzjos]

Thanks much Murray. This worked.