Consultor Eletrônico

EXISTS option on SELECT not working properly

901025-SLK01
In Versions 5 and 6, the EXISTS option on a SELECT in SQL is not fully
supported. The only time it will work is if it is the equivalent of
a CAN-FIND where it can be satisfied by an index lookup. Therefore,
the following type of code does not work properly:

SELECT customer.name, item.item-num, item.on-hand
FROM customer, item
WHERE EXISTS (SELECT * FROM order, order-line
WHERE customer.cust-num = order.cust-num AND
order.order-num = order-line.order-num AND
item.item-num = order-line.item-num AND
item.on-hand > 200).

You would expect one row to be returned from the exists for each
one of the customers, but in fact many are returned.
The following code does work properly because it is like a CAN-FIND:

SELECT name, city, st FROM customer
WHERE EXISTS (SELECT * FROM order
WHERE order.cust-num = customer.cust-num).

Here, one row is returned from the EXISTS for each of the customers.

The EXISTS option in PROGRESS SQL is not fully supported until
Version 7.

Progress Software Technical Support Note # 7301