Does somebody know how to setup a query which return inner join from 3 tables which are related as Table1(parent)->Table2(child)<-Table3(parent).
Are you thinking of something like:
FOR EACH table1, EACH table2 OF table1, table3 OF table2:
....
END.
Of course, you could create or define a query with a query string like that.
I am not so familiar with Progress 4GL syntax. Can you please write the query string in more detail.
For example if we have table "Suppliers" with primary key field "SupplierID", table "Categories" with primary key field "CategoryID" and table "Products" which is child table of "Suppliers" and "Categories" tables with primary key field "ProductID" an with foreign keys fileds "SupplierID" from "Suppliers" table and "CategoryID" from "Categories" table.
How should look like a query string which return inner join from this 3 tables?
Thanks a lot
Hi Dusan,
Well, think of a FOR EACH statement as just another type of loop, one that loops through records in a table.
You can nest loops in other loops -
FOR EACH order:
FOR EACH orderline OF order:
END.
END.
Or by concatenating them in a single statement, like so
FOR EACH order, EACH orderline OF order
So it's actually just a FOR statement
order and orderline are actually BUFFER's holding the record and its field values. You can reference the buffer fields directly in the loop.
FOR EACH order:
DISPLAY order.ordernum.
END.
Another thing you should know, if you're only reading and locking the records is not necessary add NO-LOCK for every buffer.
FOR EACH order NO-LOCK:
Just like in SELECT commands theres a WHERE clause for every nested buffer.
The OF construct mentioned is like a NATURAL JOIN.
Progress FOR statements is bascially SELECT, INSERT, UPDATE and DELETE rolled up in one statement.
Thats it for the quick intro, simple right? maybe too simple
Oh, you had a question. Here's a parent, child, parent example
FOR EACH order NO-LOCK,
EACH orderline
WHERE orderline.ordernum = order.ordernum /* OF is also an option */
NO-LOCK,
EACH item
WHERE item.itemnum = orderline.itemnum
NO-LOCK:
END.
Hi all
Thanks a lot for explanation.
Maybe I am stupid but still that not help me to setup the query that I want.
My question is:
If we have next SQL query:
SELECT *
FROM Suppliers INNER JOIN (Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
ON Suppliers.SupplierID = Products.SupplierID;
Are anybody able to write the Progress 4GL opponent query for that SQL query?
Thanks
for each Suppliers no-lock,
each Products of Suppliers
no-lock,
first Categories of Products
no-lock
by Suppliers.SupplierID
by Categories.CategoryID
by Products.ProductID:
display
Suppliers.SupplierID
Categories.CategoryID
Products.ProductID.
end.