modify parameter value before using it in query

[lexx_pangcats]

Hi all,

Does anyone know how to modify parameter value before using it to select query?

Because I just want to filter any values from parameter.

I know we can do it in the server side(java code) but the limitations are I'm not allowed to modify any codes.

So all I have to do is to depend what values are send through the parameter.

Hope you understand my issue.

 

Tnx.

[Giulio Toffoli]

This is one of the most common question, I should write a FAQ for that.

To calculate a parameter from another they must be in the right sequence. Suppose you have the parameter PARAMETER_1 The default value expression of a PARAMETER_2 can be something like: com.mypacka.MyUtility.do_somehting_with( $P{PARAMETER_1} ) 

 

Of course in the query you should use PARAMETER_2

Giulio
 

[lexx_pangcats]

what i mean is how to modify a parameter value before using it in the query.

for example i have:

$P{parameter1} = "1,2,3,...................100".

 

and my i query:

select * from table where col = ${parameter1}

 

But before I execute the query i want to change the value of the parameter.

Just like for example, when the number of characters of the parameter $P{parameter1} is greater than 8000 then im going to cut down the value of the parameter to not greater than 8000.

 

[Giulio Toffoli]

 

Default value for parameter2:

($P{parameter1} != null && $P{parameter1}.length() > 8000) ? $P{parameter1}.substring(0,8000) : $P{parameter1}

query:

select * from table where col = ${parameter2}

 

Giulio

[lexx_pangcats]

hi guilo,

 

thanks for the reply.

 

your ideas are great.this solve my issue. but it looks messy if i've to create 60 parameters.

Because in my case, there's a posibility that the number of characters inside the parameter could be in 240,000. and i think 4,000 characters only allowed to stored in every parameters. that would result to 60 parameters will be created.

 

[Giulio Toffoli]

I think this is only an SQL limitation, but indeed I don't see other solutions...if you need several different parameters in the query, you need different report parameters. Any luck using a store procedure?

Giulio