<p>Hello,</p>
<p> </p>
<p>I have the following question:</p>
<p> </p>
<p>I use a JDBC Connection and SQL Source as DataSet. The SQL Statement is very long and it lasts also very long for executing. My query is like that (it's simplified, but logical the same):</p>
<pre class="_prettyXprint _lang-">
SELECT a.name, a.city, c.id
FROM adress a, car c
WHERE a.id IN
(SELECT a.id
FROM
e LEFT JOIN v ON e.id = v.e_id
LEFT JOIN z ON z.id = v.z_id
LEFT JOIN a ON a.id = z.a_id
WHERE e.id = ? AND a.name IS NOT NULL AND v.State IN (0, 1, 2, 3, 4, 5))
ORDER BY a.name;</pre>
<p>Now I would like to make two DataSets out of it, so that I have the subquery in one statement/DataSet. This should have the background, that BIRT only has to execute the subquery ONE time, and not every time when it's going throug the statement:</p>
<pre class="_prettyXprint _lang-">
SELECT a.id
FROM
e LEFT JOIN v ON e.id = v.e_id
LEFT JOIN z ON z.id = v.z_id
LEFT JOIN a ON a.id = z.a_id
WHERE e.id = ? AND a.name IS NOT NULL AND v.State IN (0, 1, 2, 3, 4, 5)
</pre>
<p>And the rest, "main"-Statement in another DataSet. In this main-DataSet I would like to integrate the result from the subquery dataset. When it's for example "23, 45, 89, 4" then there should stand dynamically:</p>
<pre class="_prettyXprint _lang-">
SELECT a.name, a.city, c.id
FROM adress a, car c
WHERE a.id IN (23, 45, 89, 4)
ORDER BY a.name;
</pre>
<p>How can I integrate the result from one DataSet in another DataSet, when it's not just one value, as shown above? Can I creat an array or something like this in BIRT?</p>
<p> </p>
<p>Thanks already for your help!</p>
