Eclipse Community Forums: BIRT

Home » Archived » BIRT » Dynamic sql(Dynamic sql)

Dynamic sql [message #992062]

Thu, 20 December 2012 14:28

Eclipse User

I have two views, which has multiple versions. Each version is dynamically created, which is as part of design.
For the report design I need to union all the 2 views,all the versions that are available at the time of report execution.

Please find the attached report design. Here in this report I am able to exceute union all on one view.
Please suggest how to execute the same on multiple views.

Thanks
Amarnath A

[Updated on: Wed, 16 January 2013 16:11] by Moderator

Re: Dynamic sql [message #992085 is a reply to message #992062]

Thu, 20 December 2012 15:47

Eclipse User

Can you point me to where I'm looking for the issue, in the design? Is the one dataSet one view and the other dataSet the other, then the LatestRequirement dataSet is where you're trying to combine them? Let me know.

Re: Dynamic sql [message #996686 is a reply to message #992085]

Wed, 02 January 2013 12:11

Eclipse User

Hello Williams Thanks !

I need to combine both the datasets in LatestRequirement.

Thanks
AR

Re: Dynamic sql [message #996687 is a reply to message #992085]

Wed, 02 January 2013 12:11

Eclipse User

Hello Williams Thanks for the response !

I need to combine both the datasets in LatestRequirement.

Thanks
AR

Re: Dynamic sql [message #997110 is a reply to message #996687]

Thu, 03 January 2013 13:17

Eclipse User

And the script you're currently using in LatestRequirement doesn't get you what you're wanting? What is the issue? Are you getting an error? Are you only getting part of the data you want? Let me know.

Re: Dynamic sql [message #997134 is a reply to message #997110]

Thu, 03 January 2013 16:15

Eclipse User

I am not the getting the data when I am use the script for two views. I am only getting the data for one view.

I am getting the following error, when I try to modify the script.
------------------------------------------------------------------------------------
A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.6.1.v20100915
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message: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
------------------------------------------------------------------------------------

Williams can you please let me know is the script I am using right?

Thanks,
Amarnath

Re: Dynamic sql [message #997144 is a reply to message #997134]

Thu, 03 January 2013 18:00

Eclipse User

To add to the above details,

When I am trying to check the list of tables that are return in view are working fine. I don't see any issue in working with those tables.

Thanks
AR

Re: Dynamic sql [message #997274 is a reply to message #997144]

Fri, 04 January 2013 16:26

Eclipse User

What if you just returned the data from your two views separately, then used a joint dataSet to merge the two together?, rather than making even more complicated script? Or will that not work?

Re: Dynamic sql [message #997481 is a reply to message #997274]

Mon, 07 January 2013 12:51

Eclipse User

Yes Williams,

It will not work for my requirement as I need to use the data details from the views I need to use in where condition only. Going further I need to use the 3rd view from which I need to use union all on all the views that are in database.

Thanks
AR

Re: Dynamic sql [message #997521 is a reply to message #997481]

Mon, 07 January 2013 20:38

Eclipse User

So, if I create an example taking the data from more than one other dataSet to use in the main dataSet's where condition, it would help you with your issue?

Re: Dynamic sql [message #997784 is a reply to message #997521]

Tue, 08 January 2013 13:38

Eclipse User

yes it will help

Re: Dynamic sql [message #997888 is a reply to message #997521]

Tue, 08 January 2013 18:29

Eclipse User

In where coindition i need the unionl results for the tables that are resulted in the both datasets.

Please let me know if I am not clear.

Thanks
AR

Re: Dynamic sql [message #998256 is a reply to message #997888]

Wed, 09 January 2013 12:09

Eclipse User

I'm pretty sure I understand. I'll be working on an example for you, sometime this afternoon! Smile

Re: Dynamic sql [message #998295 is a reply to message #998256]

Wed, 09 January 2013 13:39

Eclipse User

I am working on the previous example provided by you. I will come up with my questions before that Smile

Thank you sir !

Re: Dynamic sql [message #998300 is a reply to message #998295]

Wed, 09 January 2013 13:49

Eclipse User

Hi Williams ,

This is the script you have give me some time back. Please let me know how I can include one more union all in the same script for the dataset.

------------------------------------------------------------------------------
myTables = reportContext.getPersistentGlobalVariable("myTables");
if(myTables[0] != "blah"){
numTables = myTables.length;
qt = "select * from " + myTables[0];
i=1;

while(i<numTables){
qt = qt + " UNION ALL select * from " + myTables[i];
i++;
}
}
else{
qt = "select * from RPT_ASSET_REQUEST_29";
}
qt = "select * from RPT_ASSET_REQUEST_29";
this.queryText = this.queryText.replace("scriptSelect",qt).replace("scriptSelect",qt);
------------------------------------------------------------------------------

Re: Dynamic sql [message #998301 is a reply to message #998256]

Wed, 09 January 2013 13:49

Eclipse User

Re: Dynamic sql [message #998347 is a reply to message #992062]

Wed, 09 January 2013 16:10

Eclipse User

Take a look at this report. It should give you an idea of how to complete your report. It takes the values from Data Set and Data Set 1 and stores them into arrays. In the Data Set 2 where clause, I left a marker to replace, though you could simply build the entire query, instead. I then union this result set with another query that uses the other marker. In the beforeOpen script, I replace the markers to give me the data I want. Hope this helps.

Attachment: UseMultipleResultSetsToFormWhereStatementForMainDS.rptdesign
(Size: 32.86KB, Downloaded 386 times)

Re: Dynamic sql [message #998368 is a reply to message #998347]

Wed, 09 January 2013 16:42

Eclipse User

Thanks for the example report Williams.

Can you please correct me if I am wrong in the attahed report.

In the attached report:

- REQUEST & PROPERTY are datasets that return the list of views that are available in schema.
- scriptselect & scriptselect1 datasets are returning the union all result for the tables that are return in the REQUEST & PROPERTY views.
- *ISSUE* dataset is the one where I am trying to fetch the data from both the views.

Thanks
AR

[Updated on: Wed, 16 January 2013 16:12] by Moderator

Re: Dynamic sql [message #998779 is a reply to message #998368]

Thu, 10 January 2013 12:29

Eclipse User

Hello Williams ,

Did you get a chance to look into this issue....

Thanks
AR

Re: Dynamic sql [message #998951 is a reply to message #998779]

Thu, 10 January 2013 21:59

Eclipse User

Can you post what the data looks like from your two dataSets and what the final query looks like in your main dataSet, when the data from your two original dataSets is added in?

Re: Dynamic sql [message #1000405 is a reply to message #998951]

Mon, 14 January 2013 12:20

Eclipse User

results for Property dataset:

[Updated on: Wed, 16 January 2013 16:12] by Moderator

Re: Dynamic sql [message #1000414 is a reply to message #998951]

Mon, 14 January 2013 12:46

Eclipse User

results for Property dataset:

[Updated on: Wed, 16 January 2013 16:13] by Moderator

Re: Dynamic sql [message #1000443 is a reply to message #998951]

Mon, 14 January 2013 14:36

Eclipse User

Williams , it seems we cannot able to call multiple queries through the script.

I tried the folowing cases:

====in script 'beforeOpen'======
qt = "select * from RPT_ASSET_REQUEST_36";
qt1 = "select * from RPT_ASSET_REQUEST_36 ";

this.queryText = this.queryText.replace("scriptSelect",qt).replace("scriptSelect",qt);
this.queryText = this.queryText.replace("scriptSelect1",qt1).replace("scriptSelect1",qt1);
=====in dataset Sql Query Text ===========

Case 1:

scriptSelect --> Working

Case 2:
scriptSelect UNION ALL scriptSelect --> Working

Case 3:

scriptSelect UNION ALL scriptSelect1 -- Not Working

The .rptdesign file is attached.

please correct me if I am wrong.

Thanks
AR

[Updated on: Wed, 16 January 2013 16:13] by Moderator

Re: Dynamic sql [message #1000936 is a reply to message #1000443]

Tue, 15 January 2013 13:12

Eclipse User

I tried with

Case 4 :

Replace both "scriptSelect" and "scriptSelect1" replaced by qt?

this.queryText = this.queryText.replace("scriptSelect1",qt).replace("scriptSelect1",qt);

It throws the error : table or view does not exist

Case 5: "scriptSelect1" and "scriptSelect2" as placeholders

this.queryText = this.queryText.replace("scriptSelect1",qt).replace("scriptSelect1",qt);
this.queryText = this.queryText.replace("scriptSelect2",qt).replace("scriptSelect2",qt);

This is working for

qt = "select * from RPT_ASSET_REQUEST_36";
qt1 = "select * from RPT_ASSET_REQUEST_36 ";

But throwing the error "table doesn't exist " when I tried the below:

qt = "select * from RPT_ASSET_REQUEST_36";
qt1 = "select * from RPT_ASSET_REQUEST_PROPERTY_36 ";

this.queryText = this.queryText.replace("scriptSelect1",qt).replace("scriptSelect1",qt);
this.queryText = this.queryText.replace("scriptSelect2",qt).replace("scriptSelect2",qt);

Re: Dynamic sql [message #1001014 is a reply to message #992062]

Tue, 15 January 2013 16:22

Eclipse User

Try this out. Look in the requirement dataSet's beforeOpen script.

Attachment: MultipleDatasets2Single.rptdesign
(Size: 43.73KB, Downloaded 451 times)

Re: Dynamic sql [message #1005831 is a reply to message #1001014]

Mon, 28 January 2013 16:54

Eclipse User

Hi Williams - Sorry for the delay in reposne.

I tried the attachment , it is giving the error table doesn't exist.

Error Description:

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.6.1.v20100915
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message: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
AR

Re: Dynamic sql [message #1006041 is a reply to message #1005831]

Tue, 29 January 2013 10:45

Eclipse User

Double check the returned values in the two dataSets we're pulling data from and make sure that all of the tables exist, in your database. I remember us having this problem the first time we set this up because your data had extra tables that weren't in your DB. Let me know.

Re: Dynamic sql [message #1006558 is a reply to message #992062]

Thu, 31 January 2013 12:52

Eclipse User

Hi Williams - issue resolved Smile

It is because of the place holders, appending 1 to one table name.

Thank you so much for ur support thru this issue.

Regards
AR

Re: Dynamic sql [message #1006785 is a reply to message #1006558]

Fri, 01 February 2013 15:18

Eclipse User

Awesome! Glad it's finally working! You're welcome for the help! Smile

Previous Topic:	How to add an editable text field ?
Next Topic:	How to localize currency symbols in BIRT in the web application?

Goto Forum:

-=] Back to Top [=-

Current Time: Fri Mar 28 06:33:37 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter