Creating Subqueries that Return a Single Value

A subquery guaranteed to return a single value is known as a scalar subquery. A standalone scalar subquery is special version of a scalar query that is effectively an expression.

Although not a requirement, standalone scalar subqueries typically use aggregate functions, in part because an aggregate function guarantees a single value. Standalone scalar subqueries are used as expressions in the Select list in most databases, and they are typically correlated.

The following is an example of a subquery that displays the headcount for each department:

SELECT dname, (select count(empno) from emp where emp.deptno = dept.deptno) FROM dept

Note that there are two Selects in this statement. The second Select is the subquery.

Note:

Using nested subqueries effectively requires some knowledge of SQL. Accordingly, we will include SQL examples to illustrate subquery concepts more clearly. SQR Production Reporting Studio shields you from having to write the actual SQL, but not from having to understand how it works.

The following steps explain how to use the SQR Production Reporting Studio Query Builder to create the SQL in the above example. The steps use sample data that you can load into your database with the loadall.sqr program included with SQR Production Reporting Studio. See ??? for information on how to load the sample data.

*  To create a subquery that returns a single value:

  1. Select Tabular Report on the main SQR Production Reporting Studio screen and connect to a database.

  2. On the Query Builder - Tables page, select the table(s) to use in the master query.

    In this example, select DEPT.

  3. On the Query Builder - Fields page, select the query fields for the master query.

    In this example, select DNAME.

    The Fields page is where you define the SELECT statement for the query. The department name selected here appears as the first SELECT statement in the corresponding SQL. (See SELECT dname in the SQL example on Creating Subqueries that Return a Single Value).

  4. Click New and select Subquery.

  5. On the Query Name page, enter a name for the subquery.

  6. On the Query Builder - Tables page, select the table(s) to use in the subquery.

    In this example, select EMP.

  7. On the Query Builder - Fields page, select the field or define the expression to use in the Select list.

    Since scalar subqueries must only return a single value, you can only select one field or enter one expression.

    Keep in mind, however, that selecting one field does not guarantee that your query will return a single value. When you select a field or define an expression on the Fields page, you must be familiar with the data or you will get an error when you run the report. One way to guarantee a single value is to use an aggregate function, such as COUNT, AVG, or SUM.

    In this example, we want to count the number of employees in each department. To do this:

    1. Click New and select Expression.

    2. Define the expression in the Expression Builder.

      In this example, to display the number of employees in each department, use the aggregate function, COUNT, on employee name.

      After you define the expression, it appears on the Fields page for the subquery.

  8. Correlate the data in the master query with the data in the subquery.

    To correlate the data, drag query fields from one table to another. Generally, you will only make one correlation; however, multiple correlation is possible for concatenated keys. In this example, correlate the data by department number (DEPTNO).

    Correlating the queries in this example is essentially saying, for every department returned by the outer query, run the second query to count the employees in that department. If you do not correlate the queries, the subquery will return only one value – the count of employees in the entire company.

    Correlating queries typically requires that one or more tables be aliased.SQR Production Reporting Studio automatically creates aliases as necessary. You can also create aliases yourself, or change the aliases created by SQR Production Reporting Studio. (SeeDefining Table Aliases for more information on creating table aliases.)

    Tip:

    To better understand correlated and uncorrelated subqueries, see the examples (correlated_subquery.srm and uncorrelated_subquery.srm) in Hyperion\products\biplus\bin\SQR\Studio\samples.

  9. Click Next and then Finish to access the Query Builder - Fields page for the master query.

    The subquery is displayed under Query Fields.

  10. Click SQL to view the SQL code generated for the query.

  11. Define how to group the report data.

    In this example, group the report by department name.

  12. On the Query Builder - Configure page, click Finish to display the report layout.