Parameter after WHERE?

[2004 IR Help]

By: Daniel B. - buedenbender

Parameter after WHERE?

2004-07-29 09:38

If got problems, when put more then one value in a parameter like...

 

Parameter: $P{Para} it's defined as java.lang.string

new String("5 or user_id = 30 or user_id = 35 [...]")

 

select * from Tab where user_id=$P!{Para}

(user_id is Number(20) field)

 

 

When use only 5 as String parameter its OK but when i use more then one users I got an SQLException:

 

ava.sql.SQLException: ORA-01722: invalid number

 

 

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)

 

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)

 

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)

 

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)

...

...

 

If you've an idee please help me!

 

Regards,

 

Daniel

 

 

 

 

 

 

By: Rick Millar - rmillar

RE: Parameter after WHERE?

2004-07-29 12:33

try changing your sql statement to be:

 

select * from Tab where user_id IN ($P!{Para})

 

and change your parameter default expression to evaluate to:

 

"'5','33','35',[...]"

 

HTH,

 

Rick

 

 

 

 

By: Daniel B. - buedenbender

RE: Parameter after WHERE?

2004-07-30 01:49

It worked. Thanks a lot!

[sergiors]

I have done the same and all I get is a completely blank report.

 

This is my SQL query:

 

select * from my_table

where my_table.numberic_field in ($P{PARAM})

 

And my parameter default value:

 

"0,1"

[jgamble]

If this is your query:

 

select * from my_table

where my_table.numberic_field in ($P{PARAM})

 

Then you are missing the "!". It should be...

 

select * from my_table

where my_table.numberic_field in ($P!{PARAM})