Using a sub-query in a report

rbuser

Is it possible to design a report such that the SQL which is generated
is of the form:

select field1, field2 from table1
where field1 in (select field3 from table2)

Is there an example of this, if it is possible?

Thanks,
Arthur

nicocizik

Hi Arthur,

If your database allows subqueries, you can enter the subquery into the
Value edit box of the Search tab in the Query Designer.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thank you for your quick response.

When I tried as you suggested, the resultant SQL (from the SQL tab) was:

SELECT asset.accountno, asset.assetno,
asset.productcode
FROM asset asset
WHERE
( asset.productcode IN
('(select','productcode','from','product','where','productcode','>','100)')
)

As you can see, RB has put single quotes around each word of the subquery.

nicocizik

Hi Arthur,

Which version of ReportBuilder are you using? In my testing with RB 10.09,
RB detected that my value was a sub-query and adjusted its "in list" SQL
formatting accordingly.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com