Multiple queries???

[bwhite]

Is there a way to do multiple queries in iReport.  Here is an arbitrary example.  I know it can be done differently, however we have queries we need to do that might require 5 queries just to process the final query:

 

CREATE TEMPORARY TABLE `FRED` (SELECT aField1, aField2 FROM `a table`);

SELECT * FROM `FRED`

[el6uap0]

I also have a need for the same thing.  I need to create two temporary tables, then execute one final query from the temporary tables to populate my report.  At the end of the report execution I drop the temp tables.

Through the use of the temp tables my query execution time goes from over three minutes to under one second.  It is largely because the datasets that I am using and joining against have over a million records.  

Tables are indexed and fairly light, however, when writing the queries with subqueries and JOINs it is simply way too slow.  The query writing really is not the issue here.  It is the fact that I cannot create and populate temp tables within my iReport query.  

PLEASE, someone, tell me that this can be done.

Basically I need to execute five queries: 

 

Code:

CREATE TEMP TABLE temp1 (LIKE big_huge_table1) ON COMMIT DROP;INSERT INTO temp1 SELECT * FROM big_huge_table1 WHERE **conditions from input controls**;CREATE TEMP TABLE temp2 (LIKE big_huge_table2) ON COMMIT DROP;INSERT INTO temp2 SELECT * FROM big_huge_table2 WHERE **conditions from input controls**;SELECT round(SELECT calculation FROM temp1 JOIN temp2) AS values,  tons_of_data FROM other_tablesJOIN more_other_tablesJOIN temp1JOIN temp2;

[hangman01]

You can use a query to create a virtual temporary table and then use that table in the from statement of the next query.

Select name, address, city, state, zip

from (Select *

          from registrations)

where name ='JONES'

 

This is a very simple example but much more complex queries can be created in the same manner. Also several virtual tables can be joined just as normal tables using the where clause.

[bwhite]

I had actually tried nesting queries like that, but it gets complicated very fast if you have more than a few queries.  I really needed the ability to create a temporary table.  What it boiled down to was I had to edit the source of both iReport and jasperReports. Since I don't have any development docs and with the added benefit of not knowing java it was a sorta hacky solution.  Since I was using jdbc, all I bothered editing was JRJdbcQueryExecuter.java.

Basically i get the query string and split it on semicolons since sql statements are separated by them.

Of course iReport will need to be edited too (and also have the new jar file from jasper), but its the same basic idea.

WARNING HACKY SOLUTION

Code:

    public JRDataSource createDatasource() throws JRException    {        JRDataSource dataSource = null;               createStatement();               if (statement != null)        {            String theQuery = statement.toString();            theQuery = theQuery.split(":", 2)[1];            String curQuery = "";            try            {                //throw new JRException("THE QUERY IS" + theQuery);                Connection con = statement.getConnection();                                              String[] queries = theQuery.split(";");                PreparedStatement statement2;                for (int i = 0; i < queries.length - 1; i++) {                    curQuery = queries[i];                    statement2 = con.prepareStatement(curQuery);                    statement2.execute();                                           }                curQuery = queries[queries.length - 1];                statement2 = con.prepareStatement(curQuery);                resultSet = statement2.executeQuery();                                              dataSource = new JRResultSetDataSource(resultSet);            }            catch (SQLException e)            {                throw new JRException("Error executing query: " + curQuery + "\n" + dataset.getName(), e);            }        }               return dataSource;    }

[cbarlow3]

Couldn't you use a common table expression (CTE) in your SQL query instead of nesting?  I think that would be more like your example of where you create explicit temporary tables.  It would look something like this:

WITH temp1 AS (SELECT * FROM big_huge_table1 WHERE conditions),

temp2 AS (SELECT * FROM big_huge_table2 WHERE conditions)

SELECT round(SELECT calculation FROM temp1 JOIN temp2) AS values,

tons_of_data FROM other_tables, etc.

 

I don't show it, but the syntax also allows you to specify the column names enclosed in a set of parens right before the word "AS" if you don't want to inherit the column names from the result set of the CTE (the SELECT in parens following "AS")

I'm pretty sure CTEs are supported in DB2 and SQL Server versions of SQL.  You'd have to see if it's supported in whatever version of SQL you're using.

Carl

 

 

[hangman01]

I might be missing something but just where are temp1 and temp2 query created? Do you do this inside the the iReport sql dialog? Are you adding xml code to the jrxml outside of iReport? With the nested tables everything is all in one area and not scattered in many places.

I have used the nested tables such as I described for so very large and complex queries and they both are easy to maintain and upgrade. Each nested query will be complete and able to produce data when run by itself in a program such as Toad (Quest) 

This allows you to work on one 'table' and not change any of the other ones.

[cbarlow3]

I've used a 'WITH' expression at the beginning of this SQL command that allows you to define temporary tables that are the result of their own SELECT statements, but that also have names and that can be reused throughout the main part of the SELECT that occurs later.  So my SQL statement is self-contained.  Below I've pasted an example that defines a temporary table named 'statenames' and another named 'populations' and then does a SELECT statement that selects specific fields from these two temporary tables, even doing a JOIN on them.  I've gone a little overboard on being self-contained in this example, because the two temporary tables I've defined are actually defined with VALUES expressions instead of a SELECT expression, but they could have both been done with SELECT expressions from your actual database.

The concept is similar to the nested queries (and in fact you can name a nested query as well by just putting (SELECT ....) as x ), but this "Common Table Expression" syntax allows you to define a few temporary tables up front that can then be used repeatedly by the overall SELECT that comes later.  The overall SELECT can have nested queries, by the way...the concepts aren't mutually exclusive.

For more examples and theory, just Google "SQL CTE"

Carl

P.S.: Just reread your question and thought I should clarify one more thing:  my suggestion is that those temporary tables, etc. all be done in the SINGLE SQL query you define within the iReport...there isn't separate xml or anything.  It's all in one place, just like nested queries.

 

Code:

Post Edited by cbarlow3 at 12/03/2010 17:37

[bwhite]

CTEs look pretty useful.  I will have to add that to my bag of tricks.  I would rather use them than maintain my own version of jasperReports and iReport.  Unfortunately in this instance, my RDBMS (MYSQL) doesn't  seem to support them.  I would definitely recommend to el6uap0 to go that route or use nested queries before going the route that I am.

The reports I am doing could be done with nested queries, but we have looked into it and it just gets too complicated with the queries we are running.  Most of the queries we are dealing with are huge clunky monstrosities being translated from an access program to be part of a new web application.

I would definitely still be interested in hearing more ideas.  It just seems surprising to me that iReport/jasper doesn't appear to provide an easy way to do this kind of thing.

[cbarlow3]

Another alternative is subreports.  You could generate your temporary table as though you were going to output it on your main report, but then instead of outputting it, you send appropriate detail information to a subreport as input parameters.  It depends on the application, of course...it wouldn't work in all cases, but I recently had a report where it was already pretty confusing figuring out how to get a list of records that met a certain criteria, and once I did that, I really didn't want to do any more ADDITIONAL JOINs, etc. in that same query, so I instead wrote a subreport that takes that record identifier as an input variable and generates the detail lines I need for each qualifying record.  Worked for me because I actually NEEDED multiple records returned and output for each qualifying record in the main query.  There's also the LIST, which allows you to do a separate subdataset, although I've never used that to interact with the main query...I've only used it when it's pretty independent data.

Carl

[TimInTX]

The secret appears to having SET NOCOUNT ON in the SQL Code.  You will get a little Red X next to this, but that does not matter.

SET NOCOUNT ON
SET ANSI_NULLS OFF
SET FMTONLY OFF

DECLARE @tblDomains Table
(
    DomainFK  int NOT NULL,
    Name  nvarchar(255) NULL,
    PRIMARY KEY (DomainFK)
)

INSERT INTO @tblDomains (DomainFK, Name)
SELECT OrgFK, Name FROM dimOrg
WHERE Code  = 'AAA' AND DomainInd = 1
--Main_Report_Query
SELECT * FROM @tblDomains

I am guessing that if this not on, the first SQL command that runs and returns something, i.e. number of rows impacted, Jaspersoft  sees that first response as the result of the query and thus responds with the “The Statement did not return a result set” because it did not, it just returned a count of impacted records.

What SET NOCOUNT ON means...
https://msdn.microsoft.com/en-us/library/ms189837.aspx