Adding A Subquery In A Report

Document ID : KB000055803
Last Modified Date : 14/02/2018
Show Technical Document Details

A subquery is a query within a query; you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

In this example the CleverPath Reporter sample database Global Sports. By default the pathname and name of the database is:

C:\Program Files\CA\SharedComponents\CleverPath Reporter\4.2\examples\samples\Global_Sports.mdb

The installation of Cleverpath reporter should have created an ODBC Data Source Name called CleverPath Reporter Sports to connect to this database.

This report will present a simple example of using a Subquery. All that will be done is to create a report that displays columns from one table and limits the output by using a search condition in another table (both tables have the column CustomerNumber).

Steps to Create the Report.

Note:

The user level must be set to Expert to complete this procedure.
Be sure to use the same data source used in the main query.

For this example:

The report "subquery subquery.rep" is provided for creating the subquery.
The report "subquery main.rep" is provided (which has the subquery added).
The subquery "subquery subquery.sql" is provide (this is what is added to the main report).

The subquery needs to be created first.

For the subquery we will use the Orders table and create a search for customers who have orders over $30,000.00. As the subquery can only return one column, the Select and Where clauses will use different columns. In this example for the subquery the column Customernumber is used in the Select and Ordertotal is used for the Where clause.

imga

In order to create the actual Subquery we need to click "Save As" button in the Edit Query window. The Save Query dialog box will default to the ReportDir setting in your prorep.prf file.

  • Highlight the Detail; choose Query, Edit to activate the Edit Query dialog.

  • Click Optimize to complete any necessary joins and apply rules to the query.

  • Click Save As, and enter a .sql file name for the subquery.

In this example we will name it "subquery subquery.sql".

imgb

No further editing is needed, so we will "OK" out of the Edit Query window. Saving this report is optional. You can also delete the detail or start from the beginning to create the main report.

Adding the Subquery to the Main Query

  • Create a new detail for the main query (including any columns, computed fields, or filtering conditions as needed).

In this example, the columns being used are from the Customer table:

customernumber, customername, city, state.

This initial report would return all rows from the Customer table.

imgc

imgd

To add the Subquery to a report:

  • Select the detail.

  • Choose Query, Filter Rows (SQL).

  • Add the left side and operator for the filtering condition.

In this example, we selected the Customernumber column.

imge

For the Condition select Equals (the "=" sign) if only one record will be returned by the subquery.

If more than one or more record is expected then almost any of the other Operators will work, however the operators Like, Begins With, and Ends With do not work with Subqueries..

To retrieve and insert the subquery use the CHOICES dropdown box and select SubQuery (SubQuery is not available here if you have selected Like, Begins With, or Ends With as the Operator).

imgf

After selecting Subquery as your Choice select the Open button to see a listing of the ReportDir directory and any stored Subqueries. Of course if you have opted to save Subqueries in another folder you would need to browse to that location.

imgg

imgh

Select the desired subquery ("subquery subquery.sql" in this example) and hit the Add button.

imgi

After hitting OK, this is what the SQL looks like (viewed from Query/Edit)

imgj

Now when the report is run we get back only those customers with orders over $30,000.

imgk

If you would like to view a sample of this report, click here:

ftp://ftp.ca.com/pub/inforeports/reports/subquery subquery.rep
ftp://ftp.ca.com/pub/inforeports/reports/subquery main.rep
ftp://ftp.ca.com/pub/inforeports/reports/subquery subquery.sql