Creating an Existence Test Subquery

*  To create a query to determine whether customers placed any orders between two specific dates:

  1. On the Query Builder - Fields page, click Where; then, click Add Clause.

  2. Select Test for non-existence via a subquery as the type of clause and click Next.

    The Query Builder is displayed, where you can enter the information for the subquery.

  3. On the Query Builder - Tables page, select ORDERS and click Next.

  4. Click Auto-Bind to correlate the master query that gets a list of customers with the subquery that determines if there are any orders.

    Clicking Auto-Bind correlates the queries by customer number. You can also correlate the tables yourself by dragging a query field from one table to another. When you click Auto-Bind SQR Production Reporting Studio correlates the queries by matching the column name and data type.

    At this point, we need to add a Where clause to specify the order dates we are interested in.

  5. Click Where to access the Where Clause Builder for the subquery, then click Add Clause.

  6. Select Evaluate a Database Column or Expression as the type of clause.

  7. Choose ORDER_DATE as the database column to evaluate.

  8. Choose BETWEEN as the qualifier.

    Remember that we are looking for customers who did not place orders between two specific dates.

  9. Enter values for the dates in the Lower Value and Upper Value fields.

    Date formats vary for different databases. For example, if you are using Oracle, enter 01-Jan-98 and 01-Jan-99. If you are using SQL Server, enter 1998-01-01 and 1999-01-01.

    You do not need to enter the single quotes required by SQL around the dates. SQR Production Reporting Studio automatically inserts the needed quotes.

  10. Click Next to view the SQL for the Where clause.

    In this example, the SQL is as follows:

    order_date BETWEEN '01–Jan-98' AND '01–Jan-99'

  11. Click Finish to return to the Where Clause Builder for the subquery and view the Where Clause you just created.

  12. Click OK, then Next, and then Finish to return to the Subquery window where you can view the SQL for the subquery.

  13. Click Next to view the finished SQL clause for the Non-Existence query.

  14. Click Finish to return to the main Where Clause Builder and view the Where clause to test for non-existence via a subquery.