Eclipse Community Forums - RDF feed
https://www.eclipse.org/forums/
Eclipse Community ForumsLoop through parameters/SQL statements
https://www.eclipse.org/forums/index.php/mv/msg/240950/726979/#msg_726979
I'm having a problem with the following task:
My first report calculates some data based on a SQL database which contains timing tests.
The user enters the testid as report parameter which is used in the Data Set SQL statement to get/calculate the values.
Quote:
SELECT *, st_mittelwert-st_min AS "st_diff_min_mw", st_max-st_mittelwert AS "st_diff_max_mw"
FROM (
SELECT st_device_groupnumber, MIN("st_waittime") AS "st_min", MAX("st_waittime") AS "st_max", AVG("st_waittime") AS "st_mittelwert", STDDEV("st_waittime") AS "st_stddev", MEDIAN("st_waittime") as "st_median"
FROM step
WHERE execution_id IN (
SELECT execution_id
FROM execution
WHERE testlauf_id = ? --<---------Parameter
)
AND st_device_text LIKE 'MP_SI%'
--ORDER BY st_device_nodenumber ASC, execution_id
GROUP BY st_device_groupnumber
ORDER BY st_device_groupnumber
)
AS "zwischentabelle"
This is working great.
But now I have to create an additional (chart)report which should contain an overview of 9 different tests. The IDs of the 9 tests should be entered by the user. So I'll get 9 different parameters. But how can I tell the SQL statement that it should use the first parameter for the first request, the second one for the second request......
I'm quiet sure that there is a simple solution, but I couldn't find it...
I'd be thankful for any hints/pointers.
Rai]]>fixed-term.raimond.eisele2011-09-20T05:49:41-00:00Re: Loop through parameters/SQL statements
https://www.eclipse.org/forums/index.php/mv/msg/240950/727194/#msg_727194
The ? in the sql are order specific, so the first will be the first
parameter, second the second parameter etc. How will the user select
the 9 parameters? Multi-select List box? If you were hard coding the 9
test ids how would you write the query? The reason I ask this is there
may be a better way to simplify the number of parameters in the query
using some script.
Jason
On 9/20/2011 1:49 AM, fixed-term.raimond.eisele wrote:
> Good morning,
>
> I'm having a problem with the following task:
>
> My first report calculates some data based on a SQL database which
> contains timing tests.
> The user enters the testid as report parameter which is used in the Data
> Set SQL statement to get/calculate the values.
>
> Quote:
>> SELECT *, st_mittelwert-st_min AS "st_diff_min_mw",
>> st_max-st_mittelwert AS "st_diff_max_mw"
>> FROM (
>> SELECT st_device_groupnumber, MIN("st_waittime") AS "st_min",
>> MAX("st_waittime") AS "st_max", AVG("st_waittime") AS "st_mittelwert",
>> STDDEV("st_waittime") AS "st_stddev", MEDIAN("st_waittime") as
>> "st_median"
>> FROM step
>> WHERE execution_id IN (
>> SELECT execution_id FROM execution
>> WHERE testlauf_id = ? --<---------Parameter
>> ) AND st_device_text LIKE 'MP_SI%'
>> --ORDER BY st_device_nodenumber ASC, execution_id
>> GROUP BY st_device_groupnumber
>> ORDER BY st_device_groupnumber
>> )
>> AS "zwischentabelle"
>
>
> This is working great.
>
> But now I have to create an additional report which should contain an
> overview of 9 different tests. The IDs of the 9 tests should be entered
> by the user. So I'll get 9 different parameters. But how can I tell the
> SQL statement that it should use the first parameter for the first
> request, the second one for the second request......
>
>
> I'm quiet sure that there is a simple solution, but I couldn't find it...
>
> I'd be thankful for any hints/pointers.
>
> Rai]]>Jason Weathersby2011-09-20T15:13:36-00:00Re: Loop through parameters/SQL statements
https://www.eclipse.org/forums/index.php/mv/msg/240950/727380/#msg_727380
thanks for your reply.
I've removed the parameter completely from the Query.
Now I'm using a short 3-line script in the chart to select the data to display.