[sairag]

For one specific requirement I need to come up a query as follows for my dataset.

select * FROM ASSET_REQUEST_29 UNION ALL
select * FROM ASSET_REQUEST_31 UNION ALL
select * FROM ASSET_REQUEST_32 UNION ALL
select * FROM ASSET_REQUEST_33 UNION ALL
select * FROM ASSET_REQUEST_34 UNION ALL
select * FROM ASSET_REQUEST_36

Problem here is there is possibility of that ifin future new version of the "ASSET_REQUEST" is created like "ASSET_REQUEST_37, ASSET_REQUEST_38" then my report won't be able to capture the informaiton from the same. I need to modify the report.

Please suggest me , how can I be able to achieve the approach where I can dynamically over come this issue.

Let me know if I need to provide any details further.

Thanks
AR

[mwilliams]

Could you use parameters to pass the range of asset_request tables you'll want to use and build the query in your dataSet's beforeOpen script?

[sairag]

Williams can you please elaborate ur answer.

So far I am not able to achieve the solution. Now I am trying to achieve: if I have tables a result of one dataset( ASSET_REQUEST_37, ASSET_REQUEST_38, ASSET_REQUEST_45, ASSET_REQUEST_52 etc ) how can I select the data from all the tables in a single dataset ?

Please let me know if any inputs need from me.

Thanks
Sairag

[mwilliams]

How do you know what Asset_Request tables you'll need in the report? Is this something that is or could be selected or entered in a parameter? If so, you could do something like this:

Parameter Selection Screen

Select Assets:
*37
*38
39
40
41
42
43
44
*45

The ones with * are selected

Then, in the beforeOpen of the dataSet you could build your query with something like:

query = "select * from ASSET_REQUEST_" + params["myParam"][0];
i=1;

while (i < params["myParam"].length){
query = query + " UNION ALL select * from ASSET_REQUEST_" + params["myParam"][i];
i++;
}

this.queryText = query;

I didn't test this, but it'd be something like this.

If the asset tables aren't selected as a parameter, how do you know which ones you need to use? Let me know.

[sairag]

Williams for your question : How do you know what Asset_Request tables you'll need in the report?

Ans: I am hitting query on all_tables/all_views to identify the tables that will help to achieve my requirement.

Using this approach I am able to get the list of tables in one dataset. Problem here is how to union all the tables in the list is the show stopper

Please help me with your suggestions.

Thanks
AR

[sairag]

Williams can you please check the following thread for the same issue.

http://www.eclipse.o...643/#msg_897643

Thanks
AR

[sairag]

Hi Williams, I am sorry. Now only I am getting slowy the above solution won't help me to achieve the requirement. I am trying this option to achieve the issue :

http://www.eclipse.o...607/#msg_895607

Please find the attahed qry for the dataset, that gives a static solution. Please let me know if any concerns.

Thanks
AR

[mwilliams]

sairag, on 24 July 2012 - 04:23 PM, said:

Williams for your question : How do you know what Asset_Request tables you'll need in the report?

Ans: I am hitting query on all_tables/all_views to identify the tables that will help to achieve my requirement.

Using this approach I am able to get the list of tables in one dataset. Problem here is how to union all the tables in the list is the show stopper

Please help me with your suggestions.

Thanks
AR

Ok, so you're returning one dataSet that has the list of tables that you want to use in your main query? Is this correct? Let me know.

[sairag]

Yes sir, this is what I am looking for.

I have attached the main query, for reference.

Please help me asap at ur convenience.

Thanks
AR

[mwilliams]

Ok. You should be able to do something like this:

In your design, put a hidden text box at the top, bound to the dataSet with the list of tables you want to access. In the beforeOpen of that dataSet, put something like:

myTables = new Array();

In the onFetch of that dataSet, put something like:

myTables[myTables.length] = row["tableNameField"];

In the beforeClose, put:

reportContext.setPersistentGlobalVariable("myTables",myTables);

In the beforeOpen of your main dataSet, you should now be able to access this array of tables, like:

myTables = reportContext.getPersistentGlobalVariable("myTables");

Now, you can step through your array and build your query, with more beforeOpen script:

i=1;
query = "select * from " + myTables[0];
while (i < myTables.length){
query = query + "UNION ALL select * from " + myTables[i];
i++;
}
this.queryText = query;

Or modify your existing queryText in a similar fashion.

[sairag]

Hi Williams - I am not able to proceed with the steps suggested. Attached the sample report design with the static solution. The cahrt is developed with the dataset which is static.

Please find the attachment.

Thanks
Amarnath

[mwilliams]

One quick question. What does row["VIEW_NAME"] actually contain? Just the number?

Like is it, ASSET_REQUEST_29 or just 29? Let me know.

[sairag]

VIEW_NAME consists the following table names:

RPT_ASSET_REQUEST_7
RPT_ASSET_REQUEST_52
RPT_ASSET_REQUEST_51
RPT_ASSET_REQUEST_50
RPT_ASSET_REQUEST_5
RPT_ASSET_REQUEST_49

Thanks
AR

[mwilliams]

Try this. Let me know if you get errors, since I can't test it.

Attached File(s)

•  DPC williams.rptdesign (62.98K)
  Number of downloads: 9

[sairag]

Hi Williams - It seems we are close to the final result. I have slightly modified the script that you have provided. Problem is the code is not reading the list of tables. To verify I have modified code with default table value. It is working.

Plz find the attached modified code.

Thanks
AR

[sairag]

Hi Williams - Thanks for the response .

It seems we are close to the final result. I have slightly modified the script that you have provided. Problem is the code is not reading the list of tables. To verify I have modified code with default table value. It is working.

Plz find the attached modified code.

Thanks
AR

[mwilliams]

Are you getting an error when you run it how I sent it to you before? I just checked my script in a sample report, using the sample database and it works as expected. Let me know.

[sairag]

Williams - I am getting the following error :

The following items have errors:


Chart (id = 657):
+ An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: PCD for HUB
Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1: ORA-00942: table or view does not exist

;
java.sql.SQLException: ORA-00942: table or view does not exist

Thanks
Amarnath

[sairag]

Williams - Please find the attached log for the same ....

[mwilliams]

Can you show me a screenshot of the ASSET_REQUEST result set? It's saying the table or view doesn't exist, so there seems to be an issue with the values we're getting from the first dataSet.