Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » BIRT » Dynamic sql(Dynamic sql)
Dynamic sql [message #992062] Thu, 20 December 2012 19:28 Go to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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 21:11]

Report message to a moderator

Re: Dynamic sql [message #992085 is a reply to message #992062] Thu, 20 December 2012 20:47 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #996686 is a reply to message #992085] Wed, 02 January 2013 17:11 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
Hello Williams Thanks !

I need to combine both the datasets in LatestRequirement.

Thanks
AR

Report message to a moderator

Re: Dynamic sql [message #996687 is a reply to message #992085] Wed, 02 January 2013 17:11 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
Hello Williams Thanks for the response !

I need to combine both the datasets in LatestRequirement.

Thanks
AR

Report message to a moderator

Re: Dynamic sql [message #997110 is a reply to message #996687] Thu, 03 January 2013 18:17 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #997134 is a reply to message #997110] Thu, 03 January 2013 21:15 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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

Report message to a moderator

Re: Dynamic sql [message #997144 is a reply to message #997134] Thu, 03 January 2013 23:00 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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

Report message to a moderator

Re: Dynamic sql [message #997274 is a reply to message #997144] Fri, 04 January 2013 21:26 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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?

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #997481 is a reply to message #997274] Mon, 07 January 2013 17:51 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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

Report message to a moderator

Re: Dynamic sql [message #997521 is a reply to message #997481] Tue, 08 January 2013 01:38 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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?

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #997784 is a reply to message #997521] Tue, 08 January 2013 18:38 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
yes it will help

Report message to a moderator

Re: Dynamic sql [message #997888 is a reply to message #997521] Tue, 08 January 2013 23:29 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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

Report message to a moderator

Re: Dynamic sql [message #998256 is a reply to message #997888] Wed, 09 January 2013 17:09 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
I'm pretty sure I understand. I'll be working on an example for you, sometime this afternoon! Smile

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #998295 is a reply to message #998256] Wed, 09 January 2013 18:39 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
I am working on the previous example provided by you. I will come up with my questions before that Smile

Thank you sir !

Report message to a moderator

Re: Dynamic sql [message #998300 is a reply to message #998295] Wed, 09 January 2013 18:49 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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);
------------------------------------------------------------------------------

Report message to a moderator

Re: Dynamic sql [message #998301 is a reply to message #998256] Wed, 09 January 2013 18:49 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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);
------------------------------------------------------------------------------

Report message to a moderator

Re: Dynamic sql [message #998347 is a reply to message #992062] Wed, 09 January 2013 21:10 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #998368 is a reply to message #998347] Wed, 09 January 2013 21:42 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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 21:12]

Report message to a moderator

Re: Dynamic sql [message #998779 is a reply to message #998368] Thu, 10 January 2013 17:29 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
Hello Williams ,

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

Thanks
AR

Report message to a moderator

Re: Dynamic sql [message #998951 is a reply to message #998779] Fri, 11 January 2013 02:59 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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?

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #1000405 is a reply to message #998951] Mon, 14 January 2013 17:20 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
results for Property dataset:

[Updated on: Wed, 16 January 2013 21:12]

Report message to a moderator

Re: Dynamic sql [message #1000414 is a reply to message #998951] Mon, 14 January 2013 17:46 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
results for Property dataset:

[Updated on: Wed, 16 January 2013 21:13]

Report message to a moderator

Re: Dynamic sql [message #1000443 is a reply to message #998951] Mon, 14 January 2013 19:36 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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 21:13]

Report message to a moderator

Re: Dynamic sql [message #1000936 is a reply to message #1000443] Tue, 15 January 2013 18:12 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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);

Report message to a moderator

Re: Dynamic sql [message #1001014 is a reply to message #992062] Tue, 15 January 2013 21:22 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
Try this out. Look in the requirement dataSet's beforeOpen script.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #1005831 is a reply to message #1001014] Mon, 28 January 2013 21:54 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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

Report message to a moderator

Re: Dynamic sql [message #1006041 is a reply to message #1005831] Tue, 29 January 2013 15:45 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
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.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Dynamic sql [message #1006558 is a reply to message #992062] Thu, 31 January 2013 17:52 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
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

Report message to a moderator

Re: Dynamic sql [message #1006785 is a reply to message #1006558] Fri, 01 February 2013 20:18 Go to previous message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member
Awesome! Glad it's finally working! You're welcome for the help! Smile

Michael

Developer Evangelist, Silanis

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 13:07:54 GMT 2024

Powered by FUDForum. Page generated in 0.09605 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly