Multi select query to SQL

[scottmacpherson]

I am trying to use a multi select query in one of my reports, but cannot get the results of the user input to pass to the report's SQL statement correctly.

I understand that in order to achieve this I need to convert the java.util.Collection parameter into a string, and at the same time make this string SQL friendly (i.e. in the format "'A', 'B',...".

There are several posts in this forum which almost answer my question, but not quite! Can anyone lead me in the right direction?

Many thanks.

[wbizzare]

I did not quite understand the Collection part of your message. However, it sounds like you are trying to modify a WHERE statement with a parameter pasted into the report. If so try this:

 

AND yourDBcolumnName $P!{p_in_clause}

 

 

where p_in_clause is set to IN ('value1','value2',..)

[scottmacpherson]

Yep, that's exactly what I'm trying to do wbizzare, but I don't know how to format a parameter in the way required for the SQL statement.

If I use the following in my SQL statement:

Code:

SELECT...WHERE column1 IN $P!{parameter}

(where parameter stores the result of the multi-select), it is parsed as follows:

Code:

[code]SELECT...WHERE column1 IN ([A, B, C])

What I want is for the parameter to be sorted out so that the final outcome is:

Code:

[code]SELECT...WHERE column1 IN ('A', 'B', 'C')

It is this reformatting of the parameter value that I don't know how to do. I've got virtually no Java experience, which isn't helping my case!

[csbac]

Hi!

I use a second parameter which is calculated from the first one ...

 

The first on is a Collection of Integers that are entered by the user via an input control (SQL Multi-Select), called MultiTaskInput.

Type is java.util.Collection.

It has default value of

Code:

Arrays.asList(new Integer[] {
  Integer.valueOf(1), Integer.valueOf(2)
})

 

The other parameter is of type String and calculated (defaultValueExpression) via

Code:

[code] $P{MultiTaskInput}.toString().replaceAll("[\[\]]", ""«»)

(there are two backslashes before the [ and two again before the ]).

 

This is for integer, of course ... in case of Strings, the following might work (I can't test it now):

 

Code:

[code] $P{MultiTaskInput}.toString().replaceAll("[\[\]]", "'"«»).replaceAll(", ", "', '"«»);

(replace the leading [ and trailing ] with ', and all , with ', ' - not very robust, of course, so it can only be done if one can rely on the string values provided (no direct user entry).

 

On the other hand, in SQL, using IDs (integer) and a lookup-table for this kind of select is usually cleaner.

 

Hope this helps,

Sebastian

Post edited by: csbac, at: 2007/03/29 05:02

[scottmacpherson]

You're a star - thank you very much! I was afraid it was going to be a bit more complicated than that :)