OPEN QUERY statement problem - Forum - OpenEdge General - Progress Community

OPEN QUERY statement problem

 Forum

OPEN QUERY statement problem

This question is answered

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

Regards

Andrzej

Verified Answer
  • 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
  • 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

  • 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.
  • Thank you very much Steve. Fantastic trick.