Dynamic sql [message #992062] |
Thu, 20 December 2012 19:28 |
A R 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 |
|
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
|
|
|
|
|
Re: Dynamic sql [message #997110 is a reply to message #996687] |
Thu, 03 January 2013 18:17 |
|
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
|
|
|
|
|
Re: Dynamic sql [message #997274 is a reply to message #997144] |
Fri, 04 January 2013 21:26 |
|
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
|
|
|
|
Re: Dynamic sql [message #997521 is a reply to message #997481] |
Tue, 08 January 2013 01:38 |
|
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
|
|
|
|
|
|
|
|
|
Re: Dynamic sql [message #998347 is a reply to message #992062] |
Wed, 09 January 2013 21:10 |
|
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
|
|
|
Re: Dynamic sql [message #998368 is a reply to message #998347] |
Wed, 09 January 2013 21:42 |
A R 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 #998951 is a reply to message #998779] |
Fri, 11 January 2013 02:59 |
|
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
|
|
|
|
|
Re: Dynamic sql [message #1000443 is a reply to message #998951] |
Mon, 14 January 2013 19:36 |
A R 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 |
A R 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);
|
|
|
|
|
Re: Dynamic sql [message #1006041 is a reply to message #1005831] |
Tue, 29 January 2013 15:45 |
|
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
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.06454 seconds