Complex Join Queries

use a first outer-join for the status you don't want,

combined with a first where not avail and status  you want

something like this

define buffer borderline for orderline.

for each order no-lock,
first orderline   no-lock outer-join where and orderline.line-status = 'grn',
first borderline no-lock                 where and not avail orderline and borderline.line-status = 'del'.

Jim,

Thanks for responding but I am still struggling as your helpful solution requires an extra buffer which I can't define within a QUERY-PREPARE. What makes my problem really difficult is that I am trying to create a user defined query within an existing application that I do not have the source code for. The application allows a user defined query to be defined that it passes to QUERY-PREPARE to extract rows into a browse list for further action. The limitation on what QUERY-PREPARE can do means that I may have to find another way to achieve what I want.

Thanks again for the response but this is a hard nut to crack.

Bernard

Can't you achieve this with dynamic query ?

Than you can set buffers as many as you want

create query vhQuery.

vhQuery:set-buffers( ....).

vhQuery:query-prepare(...).

Do not forget to delete query afterwards

Gerd

Gerd,

I am sure that your solution would work in a program but I am working with an application that I do not have the source for. This application allows me to provide a 'user defined query' by completing a text box which it passes to QUERY-PREPARE. My problem is to be able to pack into a QUERY-PREPARE statement everything I need to identify the required rows. As I need to include orders with more than one order-line status I am coming to the conclusion that it is not possible via QUERY-PREPARE. I have tried an SQL statement but this is not supported by QUERY-PREPARE so I am doing some lateral thinking to come up with a solution outside the application.

Thanks again for responding, I do appreciate the help that you have offered.

Bernard

It is sounding likely that your requirement exceeds the capabilities of the system.  Do you have a development license?  If so, you could always write something independent of the supplied system.

Thomas,

I have come to the conclusion that I am trying to do too much with QUERY-PREPARE.

My solution has been to develop an external script that selects a list of orders that match the complex critera. It then passes this list to the part of the application that allows an external list of orders to be browsed and processed. Its not the elegant solution I was hoping for but it works.

As a previous SQL developer I believe that I could have developed an SQL statement using table aliases that would have allowed me to construct a single large query that would have returned the results I wanted. My limitation with this issue is that I am not able to modify the application which only allows QUERY-PREPARE to be used to implement a user defined query.

Thank you all for the advice and guidance offered, Progress Communities has been well worth joining.

Bernard

If you know what you would do in SQL, you might look into views ... assuming that they would let you add to the DB, even though you don't have source.

Thomas,

I may do this in the future but for now my customer is more than happy with my solution as it returns results faster than the application and provides an easily modifiable solution using straightforward FOR EACH loops. A dense and complex SQL statement can be much harder to maintain.

Thanks again for your helpful contributions.

Bernard

Bernard.Ashton wrote:

Jim,
Thanks for responding but I am still struggling as your helpful solution requires an extra buffer which I can't define within a QUERY-PREPARE. What makes my problem really difficult is that I am trying to create a user defined query within an existing application that I do not have the source code for. The application allows a user defined query to be defined that it passes to QUERY-PREPARE to extract rows into a browse list for further action. The limitation on what QUERY-PREPARE can do means that I may have to find another way to achieve what I want.
Thanks again for the response but this is a hard nut to crack.
Bernard

Bernard,

If you can access the QUERY-PREPARE, you access the query definition. In order to change the query prepare, you will need a handle to the query (and don't worry if all you see is a static query reference in the QUERY qryBlah format; you can swap easily between them). Once you have a query handle, you can close, modify - in terms of adding/removing buffers, changin the prepare string - and reopen the query.

-- peter

pjudge wrote:
Once you have a query handle, you can close, modify - in terms of adding/removing buffers, changin the prepare string - and reopen the query

Not for static queries - I tried the following code

DEFINE TEMP-TABLE tt-alpha  NO-UNDO
    FIELD f1 AS CHARACTER.

DEFINE TEMP-TABLE tt-beta   NO-UNDO
    FIELD f1 AS CHARACTER.

DEFINE VARIABLE cur-qhdl    AS HANDLE   NO-UNDO.

DEFINE QUERY q-name
    FOR tt-alpha.

ASSIGN
    cur-qhdl = (QUERY q-name:HANDLE).

cur-qhdl:SET-BUFFERS(BUFFER tt-beta:HANDLE).

and got this back:

ADD/SET-BUFFERS may not be used on static query q-name. (7317)