31.4 Create a Query That Returns the Items in the Order

The steps in this section will show you how to manually create a query in the Data Model view that will return the items in the customer's order. This data retrieved will be used to display the order details in the check stub.

To manually create a query:

1. In the Data Model view, click the SQL Query tool in the tool palette, then click an open area in the Data Model view to display the SQL Query Statement dialog box.

2. In the SQL Query Statement field, enter the following SELECT statement:

   SELECT ALL
       ORDER_ITEMS.LINE_ITEM_ID,
       ORDER_ITEMS.ORDER_ID,
       ORDER_ITEMS.PRODUCT_ID,
       ORDER_ITEMS.UNIT_PRICE,
       ORDER_ITEMS.QUANTITY,
       PRODUCT_INFORMATION.PRODUCT_NAME
   FROM ORDER_ITEMS, PRODUCT_INFORMATION
   WHERE PRODUCT_INFORMATION.PRODUCT_ID=ORDER_ITEMS.PRODUCT_ID
   ORDER BY ORDER_ITEMS.LINE_ITEM_ID ASC
   

   
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called spellcash_code.txt into the SQL Query Statement field. Click Query Builder to build the query without entering any code manually. Type the code in the SQL Query Statement field.

   
   

3. Click OK. Your query displays in the Data Model view, and should look something like this:

   Figure 31-8 Data Model with two queries

   
   Description of "Figure 31-8 Data Model with two queries"
   
   

4. In the Data Model view, click the Data Link tool in the tool palette.

5. In the first query, Q_1, under G_ORDER_ID, click ORDER_ID, then drag the line to ORDER_ID1 in Q_2.

6. Your data model should now look like this:

   Figure 31-9 Data Link between two queries

   
   Description of "Figure 31-9 Data Link between two queries"
   
   

7. Save your report.