OPEN QUERY statement problem

Posted by Andrzej_Sz on 14-Sep-2016 15:10

In Sports database, how can I select ONLY 'Customers' with no 'Orders' in one OPEN QUERY statement ?

Regards

Andrzej

OpenEdge General - Forum
Posted by Steve Moore on 15-Sep-2016 03:10

This is a useful trick I found many years ago that seems to work:

define temp-table ttNoOrder
       field field1 as char.

create ttNoOrder.

define query q1 for Customer, Order, ttNoOrder.

open query q1 for each Customer no-lock,
     first Order of Customer outer-join no-lock,
     first ttNoOrder where not available(Order).

get first q1.

repeat while not query-off-end("q1"):
   display Customer.CustNum Customer.Name available(Order).
   get next q1.
end.

All Replies

Posted by marian.edu on 15-Sep-2016 01:52

normally you could but apparently it doesn’t really work, or I just expect it to behave differently :)


OPEN QUERY q FOR EACH customer, FIRST order OUTER-JOIN OF customer WHERE order.custnum = ?.

DEF VAR icnt AS INTEGER.

DO WHILE TRUE:
    GET NEXT q NO-LOCK.

    IF NOT AVAILABLE(customer) THEN
        LEAVE.
    
    /* did expect that to be filtered already, customers with no orders here */
    IF order.custnum = ? THEN
      icnt = icnt + 1.
    
END.

MESSAGE icnt VIEW-AS ALERT-BOX.


Marian Edu

Acorn IT 
+40 740 036 212

Posted by Steve Moore on 15-Sep-2016 03:10

This is a useful trick I found many years ago that seems to work:

define temp-table ttNoOrder
       field field1 as char.

create ttNoOrder.

define query q1 for Customer, Order, ttNoOrder.

open query q1 for each Customer no-lock,
     first Order of Customer outer-join no-lock,
     first ttNoOrder where not available(Order).

get first q1.

repeat while not query-off-end("q1"):
   display Customer.CustNum Customer.Name available(Order).
   get next q1.
end.

Posted by Andrzej_Sz on 15-Sep-2016 13:38

Thank you very much Steve. Fantastic trick.

This thread is closed