Use a Maintenance table in another dataset

I need to be able to run some sql to create a maintenance table using the parameters entered by the user. The results of this dataset will then need to be selected with additional sql logic to create the output for the report.

This is not a case of a sub-report because I do not want to base the population of the output on the rows from the maintenance, but on the whole dataset output.

The SQL statements for the maintenance table and the output population are very long and complex so using a sub-select is not feasible.

Any suggestions or solutions would be very much appreciated.

Thanks.

Here is an example of what I need:

Skip create if exists
create table report_mt
insert into report_mt with where stmt to reduce recordset

dataset to populate table in output
select from report_mt where sessionid = USERENV('SESSIONID')

remove from report_mt where sessionid = sessionid

Find more posts tagged with

Comments

johnw

You will need to use some scripting. I would, using the onFetch method, create a HashMap, with a List for your columns, to substitute your "table". 
 
Then using a Scripted Data source, return the results of the Map. 
 
John 
 
<blockquote class='ipsBlockquote' data-author="'cmrdjr'" data-cid="66648" data-time="1279811176" data-date="22 July 2010 - 08:06 AM">
I need to be able to run some sql to create a maintenance table using the parameters entered by the user. The results of this dataset will then need to be selected with additional sql logic to create the output for the report. 
 
This is not a case of a sub-report because I do not want to base the population of the output on the rows from the maintenance, but on the whole dataset output. 
 
 
The SQL statements for the maintenance table and the output population are very long and complex so using a sub-select is not feasible. 
 
 
Any suggestions or solutions would be very much appreciated. 
 
Thanks. 
 
Here is an example of what I need: 
 
Skip create if exists 
create table report_mt 
insert into report_mt with where stmt to reduce recordset 
 
dataset to populate table in output 
select from report_mt where sessionid = USERENV('SESSIONID') 
 
remove from report_mt where sessionid = sessionid </blockquote>

Hans_vd

Did you consider having a stored procedure to base your dataset on?
This seems a perfect candidate for it.

If your database supports session based temporary tables, you can skip the session logic and have the database automatically take care of that. Is it an Oracle database you have?

cmrdjr

Thanks folks.

JohnW,
I do not see how a hashMap will help. I do not need a list of columns, I need a dataset to be manipulated by another dataset. The first query can produce anywhere from zero to 100,000 or more rows and must be manipulated with another query to be output in the report. Since the underlying table is a general ledger with entries back to 1995, bringing back the whole result set is not possible. I have to restrict the results with parameters. The SQL is very complex in both queries.

Hans_vd,
Yes I am working in Oracle and using a stored proceudre is not really possible. Our DBAs do not allow us to create stored procedures. I do not see how that would help anyway as I must restrict the output of the first query with parms entered at run time.

Thanks,

johnw

With the HashMap, your not getting a list of columns, your just keying each entry to a unique ID. If you use a ArrayList or some other Java collection, you use a named parameter list to work as your column names.

If that wont work for whatever reason, you can think about doing a straight JDBC call to populate your before table, and pass that around as a global variable. Run this in either the initialize or before factory event. Then use the Scripted Data Set as your only data set in your report.

But if for whatever reason you dont think that it is technically possible, I would recommend maybe running some sort of ETL tool before your actual report execution (say in the initialize or before factory event) to get that underlying table, or possibly as a 2 part process in a scheduled task. Talend can produce Java classes that will do your ETL process, and you can call the result from your initialize event in your report. Same with a Kettle/Pentaho transform. You call the ETL first, then have BIRT report off of the second table.

John

Hans_vd

<blockquote class='ipsBlockquote' >I do not see how that would help anyway as I must restrict the output of the first query with parms entered at run time. 
 
Thanks, </blockquote>
 
 
I read about your DBA's not letting you do this, but if you could, that solution would be perfectly possible. Stored procedures can take parameters just as any other type of dataset can. 
 
good luck, and please post a comment on your final solution when you have one 
Hans