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.
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.
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.
Click OK. Your query displays in the Data Model view, and should look something like this:
In the Data Model view, click the Data Link tool in the tool palette.
In the first query, Q_1, under G_ORDER_ID, click ORDER_ID, then drag the line to ORDER_ID1 in Q_2.
Your data model should now look like this:
Figure 31-9 Data Link between two queries
Save your report.