SQL Query for each row

Hi!

I need to execute a SQL Query to each row fetched by the current data set. It's possible? How?

I'm reading two BIRT books (A Field Guide to Reporting and Creating and Extending BIRT) but still not founded nothing about that.

Please, a light!

Find more posts tagged with

Comments

brunocobra

Searching a little more, my problem could be solved putting a DataSet into other DataSet.

However, in the "lower DataSet" I need to get rows of the "top DataSet". Please, how can I do that?

brunocobra

Again, nobody helps... how always...

mwilliams

Hi brunocobra,

Can you explain what you're trying to do a little further? Thanks.

brunocobra

I need to execute an SQL Query for each row returned of a DataSet. For this, I'm trying to execute other DataSet (that contains this SQL Query) into this "main DataSet", but looks like impossible pass parameters for one to other.

Do you understand? It's simple like that, but looks like Birt wanna complicate my life, hehe.

mwilliams

brunocobra,

Sorry, I still don't know if I'm clear. Is there any way you can post a sample of what your data looks like in your datasets and what you're wanting to do with them, i.e. how you want the output to look, etc. If you can set up something similar to your issue with the sample database, that could make it easier for me to look at as well. Thanks.

brunocobra

Hehe ok, let me try explain again. So, you could imagine the following situation:

- I have two DataSets (States [query = "SELECT * FROM states"] and Cities [query = "SELECT * FROM cities WHERE state = ****"]).
- My objective is show the States and, into them, the Cities.
- For do that, I put the States DataSet into the page. For now, I'd showing only States.
- Now, I put the Cities DataSet into detail row of the States DataSet. However, I need that this DataSet (Cities) get an actual value of row of States DataSet, to substitute the **** in Cities DataSet Query. If I did that, I'm showing all cities to these State after each row of the State.

Do you understand now? It's more simple that looks like.
Before other thing, I know that this example could perfectly solved with only one DataSet and/or one Query. BUT this case it's ONLY an extremally basic example for my necessities. Unfortunetally, I'm needing urgently a solution...

mwilliams

brunocobra,

I don't know that calling the query for each row is possible or if you'd want to do that anyhow. Keeping with the city/state example, I set up a simple example with the sample database how you'd use two dataSets to have an outer table and an inner detail table from another dataSet as a subreport. You would just use a filter on the inner table to link the key between the two dataSets.

I pulled the states out of the customer table for 1 dataSet and the city and state out of the same table for the 2nd dataSet. I set up a table with the state dataSet and grouped it by state. I deleted the detail row and put the other table in the group footer row, linked to the cities dataSet. I then set a filter on the inner table that linked state from the state dataSet and state from the cities dataSet. Let me know if this helps at all or if you have any questions.

This example was created in BIRT 2.3.0

brunocobra

Hi, mwilliams!

Firstly, very thanks for your helps. I tested your example and I learned about row._outer, that I don't knew and can help me, maybe... Your example works fine with this case, with little data returned.

However, I tried to apply this with my real case (when State DataSet represents one table that contains more than 1.000.000 of data, and Cities DataSet represents one table that contains close of 8.000.000 of data) and this doesn't work, because it's did very hard use 2 DataSet with anything like that "select * from table".

Any ideia of how correct this? It was possible to advance a little... thanks

mwilliams

brunocobra,

What is the reason for it not working with your real situation? The time it takes to run?

brunocobra

Because, with this way, are returned almost 8.000.000 datas FOR EACH ONE of 1.000.000 datas and only after that Birt filter.

What I want is return, for each one of 1.000.000 datas, ONLY and directly your associated datas (of the 8.000.000), without that Birt did this filter through your own filter.

mwilliams

brunocobra,

Alright. Try this one out. This one will run a separate SQL for each State. The trick is tying the dataSet parameter in the Cities dataSet with the State from the States dataSet by clicking on the binding tab in the property editor for the subreport table (cities), clicking the "dataset parameter binding..." button, and making the value for the parameter row["State"].

brunocobra

It's really incredible!

I don't believe that this is so simple, I ever tried everything! Man, MANY MANY MANY THANKS for your help! It's exactly what I need!

Extremely grateful.

mwilliams

No problem, glad I could help. Let us know whenever you have questions.

NascoteChris

I know this is a very old thread but I hope you are still out there ...

First let me say that this thread has been helpful for a newbie like me. Second can you explain the "I tested your example and I learned about row._outer, " what is row._outer and where does it fit in. What does it do? I have downloaded the example and tested.

Obviously, I came across this topic because I am having the exact issue and it's driving me crazy.
This is my sql for the subreport dataset. It returns records if I hard code a refwo in. But none with the parm and none if I preview in the Data Set Editor (after adding the parm).
___________________________________________________________________________________________________
LaborWorkPerformed = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
LaborWorkPerformed.open();

var sqlText = new String();
;

sqlText = " SELECt refwo, startdate, laborcode, lt7, ldtext "
+ " from labtrans "
+ " left outer join longdescription on labtrans.labtransid = longdescription.ldkey "
+ " and longdescription.ldownertable = 'LABTRANS' and longdescription.ldownercol = 'LT7' "
+ " where lt7 = 1 "
+ " and refwo = ? "
;
LaborWorkPerformed.setQuery(sqlText);
___________________________________________________________________________________________________

I have attached my file if you would please help.. thanx

mwilliams

row._outer is how you can access the binding of the parent table from an embedded table. You can pass this value to a filter, to limit your embedded table, or you can pass it through the dataSet parameter of the embedded table to limit the embedded table's data through SQL, rather than a filter. Hope this answers your question.

NascoteChris

Pardon my ignorance, but is an embedded table the same thing as a subreport in BIRT speak? and how is it passed - the row._outer, that is? example? 
 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="107609" data-time="1343337272" data-date="26 July 2012 - 02:14 PM">
row._outer is how you can access the binding of the parent table from an embedded table. You can pass this value to a filter, to limit your embedded table, or you can pass it through the dataSet parameter of the embedded table to limit the embedded table's data through SQL, rather than a filter. Hope this answers your question. </blockquote>

mwilliams

Yeah. Just a sub table. Example:

You have 2 queries. One for the outer table and one for the sub report table.

Main query:

select customername from customers

Inner query:

select * from orders where customername = ?

The '?' will create a dataSet parameter in your inner dataSet.

If you drag your main dataSet into the layout, then drag the inner dataSet into the detail row of the main table, you can then select the sub table and go to the binding tab of the property editor. Here, you'll see a button that says dataSet parameter binding. If you click on it, you can select your dataSet parameter from the drop down and then use the expression builder to pass a value to it. Under the available column bindings, you'll have access to the outer table. If you select the outer table and then select the customername field, row._outer["customername"] will now appear in the box. Click ok and run the report. The outer table's value will now be passed to the inner table's query for each row of the outer table, returning the correct subtable results.

Hopefully this explains it better. Let me know if you need an actual example design.