Consultor Eletrônico

Status: Unverified

GOAL:

How to translate a nested FOR EACH 4GL query to an SQL-92 query?

FIX:

The following SQL-92 query:

SELECT
Customer.CustNum,
Order.Ordernum,
Orderline.Itemnum,
Item.Itemnum,
Item.Price
FROM
customer,
order,
orderline,
item
WHERE
Customer.Country = 'USA' AND
Order.CustNum = Customer.CustNum AND
OrderLine.Ordernum = Order.Ordernum AND
Item.Itemnum = OrderLine.Itemnum AND
Item.Price > 2499
ORDER BY
Customer.CustNum,
Order.Ordernum,
Orderline.Itemnum,
Item.Itemnum,
Item.Price;

returns the same result set as the following nested FOR EACH query:

FOR EACH customer WHERE Customer.Country = "USA",
EACH order WHERE Order.CustNum = Customer.CustNum,
EACH orderline WHERE OrderLine.Ordernum = Order.Ordernum,
EACH ITEM WHERE Item.Itemnum = OrderLine.Itemnum
AND Item.Price > 2499
BY Customer.CustNum
BY Order.Ordernum
BY Orderline.Itemnum
BY Item.Itemnum
BY Item.Price:
DISPLAY
Customer.CustNum
Order.Ordernum
Orderline.Itemnum
Item.Itemnum
Item.Price WITH STREAM-IO.
END.