Skip Headers
Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports
11g Release 1 (11.1.1)

Part Number B32122-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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 follows
    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 follows
    Description of "Figure 31-9 Data Link between two queries"

  7. Save your report.