Creating a WHERE clause

 

With a WHERE clause on a table or a view, you can display only records that match certain criteria. In this example, you only want to view those items that have been ordered in the last 14 days. To specify this information, create a WHERE clause:

  1. Click Select Rows.

  2. On the Select Rows window, enter the following command: WHERE LAST_ORDER_DATE > CURRENT DATE - 14 DAYS. You can select the elements that make up this WHERE clause by selecting them from the options shown, or you can enter them in the Clause field.

  3. Click OK.

  4. To view the SQL used to generate this view, click Show SQL.

  5. Click OK to create the view.

    screen capture of select rows window

  6. To display the contents of RECENT_ORDERS, right-click RECENT_ORDERS and select View Contents.
You should see the following information displayed.

ITEM_NUMBER LAST_ORDER_DATE QUANTITY_ON_HAND
303476 2003-09-22 100

In the preceding example, the columns in the view have the same names as the columns in the table because you did not specify new names. You can create the view into any schema, not necessarily into the schema that contains the table it is built over.

 

Parent topic:

Creating and using a view