parameter with all values

[megotronx]

how to pass all values with parameter in query?

for example

 

select x1,x2

from table

where

x1='value'

 

it may be the exact value or all values

Post edited by: megotronx, at: 2007/07/02 12:50

[jmurray]

If you want to pass multiple values to an SQL query then you need to modify the query. Try this:

 

 

select x1,x2

from table

where

x1 IN ( $P!{valueList} )

 

 

$P{valueList} is a java.lang.String paremeter that contains comma separated values. For strings it would look like "'value1', 'value2', ... " For numbers it would look like "1, 2, 3, 5 ...."

 

 

Works for Oracle and mySQL. Don't know about other dB types.

 

 

Note the use of the exclamation mark which suppresses the rabbit ears (") around the string.

 

 

Note2: Make sure that your parameter contains at least one value, otherwise the SQL will fail with error

 

 

.

Post edited by: jmurray, at: 2007/07/02 22:47

[megotronx]

thats fine.But i want to know is at this tool something like wildcard function in other reporting tools.For example i want to search in database for records which starts with N.in other tools Wildcard(N*).

is smthing similar to this? or search all records in database (wildcard(*))????

thanks

 

your suggestion is good.But for example if db is growing with several thousands records a day and to put all values in to array is suicide.any suggestions?

[jmurray]

Pattern match using wildcards % and ?

 

 

select x1,x2

from table

where

x1 LIKE '$P!{keystring}%'

 

 

works for Oracle and mySQL for strings, so if you are dealing with number patterns you'll need to convert them to string first. Here's an Oracle example:

 

 

select x1,x2

from table

where

TO_CHAR(x1) LIKE TO_CHAR($P{keynumber}) || '%'

 

 

 

% substitutes for any number of characters, ? for a single character.

 

 

 

.

Post edited by: jmurray, at: 2007/07/04 02:25

[megotronx]

Thank you thank you thank you

That is what I wanted :woohoo: