Can we use multiple tables in QUERY-PREPARE()?

Question

I received a suggestion to take a look at QUERY-PREPARE(). I saw an example of how to use it, tweaked it and made it so I could use it. All was good until I tried adding another table into the mix. I'll put my code in here.

/* Definitions */
DEFINE VARIABLE cQuery       AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cInString    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cOrStatement AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iEntry       AS INTEGER     NO-UNDO.

/* Defining a temp-table to query */
DEFINE TEMP-TABLE client
    FIELD clientId AS INTEGER
    FIELD city     AS CHARACTER.

DEFINE TEMP-TABLE country
FIELD country AS CHARACTER
FIELD city AS CHARACTER.

    /* my temp-table to test it*/
DEF TEMP-TABLE tt-cliente
FIELD id LIKE client.clientId
FIELD cidade LIKE client.city
FIELD pais LIKE country.country.

/* And a query */
DEFINE QUERY qClient FOR client,country.

/* Create some bogus data */
CREATE client.
ASSIGN 
    client.clientId = 1
    client.city     = "Rome".

CREATE client.
ASSIGN 
    client.clientId = 2
    client.city     = "Barcelona".

CREATE client.
ASSIGN 
    client.clientId = 3
    client.city     = "Paris".

CREATE client.
ASSIGN 
    client.clientId = 4
    client.city     = "Prague".

CREATE client.
ASSIGN 
    client.clientId = 5
    client.city     = "São Paulo".

CREATE client.
ASSIGN 
    client.clientId = 6
    client.city     = "Rio de Janeiro".

CREATE client.
ASSIGN 
    client.clientId = 7
    client.city     = "Brasília".

CREATE client.
ASSIGN 
    client.clientId = 8
    client.city     = "Imigrante".
                        

/* Create some more bogus data */
CREATE country.
ASSIGN 
    country.country = "Italy"
    country.city     = "Rome".

CREATE country.
ASSIGN 
    country.country = "Spain"
    country.city     = "Barcelona".

CREATE country.
ASSIGN 
    country.country = "France"
    country.city     = "Paris".

CREATE country.
ASSIGN 
    country.country = "Czech Republic"
    country.city     = "Prague".

CREATE country.
ASSIGN 
    country.country = "Brazil"
    country.city     = "São Paulo".

CREATE country.
ASSIGN 
    country.country = "Argentina"
    country.city     = "Buenos Aires".

CREATE country.
ASSIGN 
    country.country = "USA"
    country.city     = "New York".

CREATE country.
ASSIGN 
    country.country = "England"
    country.city     = "London".
/* These are the cities we are searching for */
cInString = "1,5,8".

/* Convert the comma-separated list of cities to an "OR-statement" */
DO iEntry = 1 TO NUM-ENTRIES(cInString):
    cOrStatement = cOrStatement + (IF cOrStatement = "" THEN "" ELSE " OR ") + "client.clientId = " + QUOTER(ENTRY(iEntry,cInString)).
END.

/* Add () around the or-statement just to be sure */
cOrStatement = "(" + cOrStatement + ")".

/* Put together the query */
cQuery = "FOR EACH client WHERE " + cOrStatement.


/* My edit */
cQuery = cQuery + "AND client.clientId < 6 BY client.clientId, EACH country WHERE country.city = client.city".
/* Attach the query-string to the query */
QUERY qClient:QUERY-PREPARE(cQuery).

/* Open the query ...*/
QUERY qClient:QUERY-OPEN().

/* And get the first result */
/*GET FIRST qClient.     //Este aqui é o original*/
GET FIRST qClient.

/* Iterate through results as long as there are any... */
DO WHILE AVAILABLE client:
    CREATE tt-cliente.
    ASSIGN
    tt-cliente.id     = client.clientId
    tt-cliente.cidade = client.city
    tt-cliente.pais   = country.country.
    GET NEXT qClient.
END.

/* Close query */
QUERY qClient:QUERY-CLOSE().


FOR EACH tt-cliente:
    DISP tt-cliente.
END.

I want to create the temp-table so I can extract it with a JSON. So is there any way of doing 2 or more tables like this? Thanks?

Answer 1

The sorting (BY) should be after all joins. Then it will work:

(Linebreaks only for readability"

cQuery = cQuery + "AND client.clientId < 6," +
                  "EACH country WHERE country.city = client.city " +
                  "BY client.clientId".