EACH in a FOR EACH clarification

[Kladkul]

Hello,

I was wondering if anyone could clarify what exaclty an EACH inside of a FOR EACH does this code doesn't make a whole lot of sense to me:

FOR EACH table1 NO-LOCK, 
      EACH table2 NO-LOCK: 
 
blah blah. 
 
END.

Also where in this would the WHERE statement go if both had a common field I was searching on?

Thanks.

 

[DevTeam]

That's equivalent to "SELECT * FROM table 1, table2". Indeed it isn't much interesting in most case without a join between both tables, something like "SELECT * FROM table1, table2 WHERE table1.field1 = table2.field2".

In 4GL, you can do this with

FOR EACH table1 NO-LOCK, EACH table2  OF table1  NO-LOCK:
  /* ... */
END.

if table1 and table2 are made to work together.

A good example of EACH inside a FOR EACH is

FOR EACH customer NO-LOCK, EACH order OF customer NO-LOCK:
  /* ... */
END.

EDIT : I forgot the WHERE statement : it can be put after each "EACH", that is to say :

FOR EACH customer WHERE customer.cust-id = '01234' NO-LOCK,
      EACH order OF customer WHERE YEAR(order.ship-date) = 2008 NO-LOCK:
  /* ... */
END.

 

[Iann]

Hello Kladkul

I beleive its used when you need to get more tables than one to your buffer from database. Every EACH is for one table you want to select. This might be handle when you are for example writing some reports and need a lot of data from a lot of tables.

The WHERE phrase comes after each EACH phrase so it might look like this:

FOR EACH table1 where table1.id > 20,
EACH table2 where table2.fakeID = table1.fakeID:

Hope this helps

 

[Kladkul]

Ok, so if I put a WHERE statement in the first table and the 2nd table has that same field. Will the 2nd table only draw data that meets that WHERE clause?

FOR EACH table1 NO-LOCK WHERE table1.col1 = "hello", 
      EACH table2 NO-LOCK: 
 
blah. 
 
END.

 

[DevTeam]

Ok, so if I put a WHERE statement in the first table and the 2nd table has that same field. Will the 2nd table only draw data that meets that WHERE clause?

FOR EACH table1 NO-LOCK WHERE table1.col1 = "hello", 
      EACH table2 NO-LOCK: 
 
blah. 
 
END.

I don't think so, as there's no link between both your tables. So you'll have x rows from your first table, and your whole second table.

EDIT : and be careful, your FOR EACH will loop Number_of_rows_from_table 1 x Number_of_rows_from_table 2. I don't know the English word, but in French that's called "Produit cartésien".

 

[Kladkul]

Thanks DevTeam! I didn't know that the loop would iterate for both tables. That explains why this program that I'm working with takes much longer then when I did that.

Since there is no link between the tables in the example I listed above. What would be the best way to make that link? Use the same WHERE clause in both tables?

 

[DevTeam]

If both your tables have the same field as primary key, Progress will be able to link them when you use the "EACH ... OF" statement.

Ex :
Customer table
- CustomerNumber
- CustomerName
- CustomerAge
- ...

Order table
- CustomerNumber
- OderNumber
- OrderAmount
- ...

Then Progress will link both tables with CustomerNumber field (as if you were doing WHERE customer.CustomerNumber = Order.CustomerNumber).


If the only link between both tables is the WHERE statement
(ex : FIND Students WHERE Students.Age > 20
FIND Teachers WHERE Teachers .Age > 20)
then you have to dissociate queries in 2 distinct FOR EACH.

 

[sphipp]

FOR EACH table1 NO-LOCK WHERE table1.col1 = "hello", 
      EACH table2 NO-LOCK WHERE table2.linkfiled1 = table1.linkfield1: 

blah. 

END.

 

[DevTeam]

Here you could use the OF statement

FOR EACH table1 NO-LOCK WHERE table1.col1 = "hello", 
      EACH table2 OF table1: 
   /* blah. */
END.