Progress
Language Tutorial
for Character


Relating with WHERE

You can also relate tables with WHERE. There are two scenarios where you must use WHERE:

In both of these cases, you may want to reconsider your design. Consider adding an index or changing the name of the field.

You can also use WHERE to do any relation that OF can do. The following example uses OF:

OPEN QUERY My-Query FOR EACH Customer, EACH Order OF Customer 

This equivalent example uses WHERE:

OPEN QUERY My-Query FOR EACH Customer, 
            EACH Order WHERE Order.Cust-Num = Customer.Cust-Num. 


OF implicitly compares the keys, while WHERE explicitly compares the keys. At compile time, Progress turns an OF into a WHERE expression. For that reason, you should consider OF a shortcut and use WHERE most of the time.

Follow these steps for a demonstration of the last exercise reworked to use WHERE:

  1. Open lt-09-05.p and run it. The same display as in the last exercise appears:
  2. Scroll through the records. As you can see, the same data is present.
  3. Choose Exit, then press SPACEBAR to return to the Procedure Editor.

Here is the code that created the display:

lt-09-05.p
      /**********  DEFINE QUERIES  **********/
      DEFINE QUERY New-Query FOR Customer FIELDS (Name Cust-Num),
                     Order FIELDS (Order-Num Cust-Num).

      /**********  DEFINE WIDGETS  **********/
      DEFINE BROWSE New-Browse QUERY New-Query
          DISPLAY Name SPACE(3) Order-Num WITH 12 DOWN.
      DEFINE BUTTON btn-Exit LABEL "Exit".

      /**********  DEFINE FRAMES  **********/
      DEFINE FRAME Frame1 
          New-Browse AT ROW 1 COLUMN 2
          btn-Exit   AT ROW 1 COLUMN 50
              WITH NO-BOX CENTERED THREE-D.

      /**********  MAIN LOGIC  **********/
/*1*/  OPEN QUERY New-Query FOR EACH Customer, EACH Order 
                         WHERE Order.Cust-Num = Customer.Cust-Num BY Name.
      DISPLAY New-Browse WITH FRAME Frame1.
      ENABLE ALL WITH FRAME Frame1.
      WAIT-FOR CHOOSE OF btn-Exit. 

As shown at point 1, you still need the additional EACH to search through the second table. The WHERE expression causes an explicit mathematical comparison between key values.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095