What is the best way to handle complex SQL Queries?

aikeda

I have a report that consists of a complex SQL query made up of subqueries, for example:

Select NVL (
(SELECT SUM(amount)
FROM tbl_1 a,
tbl_2 b
WHERE a.id = b.id
AND a.month = tbl_a.month
AND a.record_type = :recordType), 0) as TBL_AMOUNT_SUM,

NVL (
(SELECT sum(total)
FROM tbl_3 a,
tbl_4 b
WHERE a.id = b.id
AND a.month = tbl_a.month,
AND a.record_type = :recordType), 0) as TBL_TOTAL_SUM

FROM tbl_a
WHERE tbl_a.month between :fromDate and :toDate
GROUP BY blah, blah, blah


Now this isn't my original code and there is the option to re-write the query if required, however as you can see:

1) the sub-queries reference the outer query
2) A single named parameter is being used in different locations (:recordType)

In Actuate eReports (which we are trying to migrate from) there doesn't appear to be much of an issue creating the crosstab with this query, but it seems impossible to re-create the report in BIRT.

BIRT 2.3 doesn't appear to support named parameters so I tried altering the beforeOpen event and rewriting the queryText attribute replacing the named parameters with params["param name"]

But it doesn't seem to help at all.

I guess what I'm getting at, Business Intelligence is all about supporting queries such as the one above, yet I can't seem to work out how to get this to work with BIRT.

Am I just naive or are there still areas that BIRT just does not support as far as Business Intelligence goes?

mwilliams

Hi aikeda,

You are correct, BIRT does not support named parameter as of yet. The way parameters are done in BIRT is by using '?' in place of the parameter value in the query. A dataSet parameter will now show up in your dataSet editor under the parameters tab which you can link to your report parameter that you created (params["param_name"]). This works for single select parameters. For multi-select parameters, there is a little more work involved.