Блог

Я получил предложение взглянуть на QUERY-PREPARE() . Я увидел пример того, как его использовать, изменил его и сделал так, чтобы я мог его использовать. Все было хорошо, пока я не попытался добавить еще одну таблицу в микс. Я добавлю сюда свой код.

 /* 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.
 

Я хочу создать временную таблицу, чтобы я мог извлечь ее с помощью JSON.
Итак, есть ли какой-нибудь способ сделать 2 или более подобных таблиц? Спасибо?

Сортировка (ПО) должна быть после всех соединений. Тогда это будет работать:

(Разрывы строк только для удобства чтения»

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