Previous Topic Next Topic

Queries

Relational databases typically contain many rows of data, with each row constituting a separate record. Most relational database queries retrieve only a portion of the records contained in a table. The WHERE clause qualifies the query command statement to limit the data to specific records from the tables.

Queries are a higher-level object in a catalog. The concept is similar to that of views in the database but they are stored in the catalog file rather than the database itself. In this way, a query is independent from the raw database since Logi JReport uses a mapping name that is unique instead of the table.column syntax of SQL. You can use queries to view, change and analyze data in different ways, and Logi JReport can help you with the building of various professional reports based on queries. When making a query, you can place criteria or restrictions on the data to extract only the required data from the database. For example, instead of having to view all the customers of your company, you can view just the customers from Japan.

You can mash up multiple data resources from different connections into a single query for more complex, deeper insights, including tables, views, synonyms, imported SQLs, imported APEs, stored procedures, user defined data sources and other existing queries, and can create distributed joins to set up inter-relationships between the data resources. Data mashup makes it possible to integrate multiple separated application systems in your enterprise so as to get more comprehensive and objective data for decision making. Distributed joins extend this by letting you access multiple data resources as one virtual data resource. The data resources added in a query are all treated the same as tables.

Logi JReport Designer includes an interactive query designer - the Query Editor, to build any queries. However, only the queries built on tables, views, and synonyms from JDBC connections can support all of the functions. If a query mashes up multiple data resources, only part of the Query Editor functions are supported for it. Logi JReport queries support a limited set of SQL 92 functions that are common to all RDBMS systems. Each vendor has many extensions to these basic functions and if you want to use them, it is best to use either imported SQL or stored procedures. When you build a query using the Query Editor and do not add features Logi JReport doesn't know how to parse, you can take advantage of Push Down Group Query to allow Logi JReport to add aggregation functions and a GROUP BY clause to specify the groups required based on the groups in the component being used. This is a big performance advantage when you use queries instead of imported SQLs, stored procedures and other query types.

In addition Logi JReport supports the Multiple Query Engine to provide smart query processing, which automatically detects schemas and splits large queries into multiple small queries that greatly improve performance. It is activated when the following conditions are satisfied:

• You have predefined links between primary keys and foreign keys in your database.
• The query contains data resources from a single connection only.
• The Push Down Group Query property is enabled.

The following topics provide more information about queries:

• Creating Queries in a Catalog
• Using Pre-joins in Queries
• Creating Union Queries
• Using a Query to Filter Multiple Queries
• Locking Queries
• Query Modifiers
• Cached Query Results
• Dynamic Queries
• Data Manager

Previous Topic Next Topic