Concatenated SELECT

[Matias Borra]

Hello,

I'm trying to carry out a query (FOR EACH, SELECT or whatever) from a table which name is in another database field.
Pseudo Code:

FOR EACH related_table.table_name NO-LOCK:
DISP related_table.field_to_show.
END.

where related_table.table_name contains the name of the Progress table that I want to query, and related_table.field_to_show contains the Progress field name of the table that I want to query.

Any idea on this?

Thanks in advance.

Matias.

 

[plus_marca]

Hi.

You can try this.

DEFINE VARIABLE L-qhandle AS HANDLE NO-UNDO.
DEFINE VARIABLE L-bhandle AS HANDLE NO-UNDO.
DEFINE VARIABLE L-fdhandle AS HANDLE NO-UNDO.
DEFINE VARIABLE L-i AS INTEGER NO-UNDO.
DEFINE VARIABLE L-tabla AS CHARACTER NO-UNDO.
DEFINE VARIABLE L-campo AS CHARACTER NO-UNDO.

FIND FIRST related_table NO-LOCK NO-ERROR.
IF AVAILABLE related_table THEN
ASSIGN L-tabla = related_table.table_name
L-campo = related_table.field_to_show.

CREATE QUERY L-qhandle.
CREATE BUFFER L-bhandle FOR TABLE L-tabla.

L-qhandle:SET-BUFFERS(L-bhandle).
L-qhandle:QUERY-PREPARE("FOR EACH " + L-bhandle:NAME).
L-qhandle:QUERY-OPEN.
REPEAT:
L-qhandle:GET-NEXT.
IF L-qhandle:QUERY-OFF-END THEN LEAVE.
DO L-i = 1 TO L-bhandle:NUM-FIELDS:
IF L-bhandle:BUFFER-FIELD(L-i):NAME = L-campo THEN
DISPLAY L-bhandle:BUFFER-FIELD(L-i):BUFFER-VALUE
WITH DOWN.
DOWN.
END. /* DO L-i = 1 TO L-bhandle:NUM-FIELDS: */
DOWN.
END. /* repeat */
L-qhandle:QUERY-CLOSE.
DELETE OBJECT L-qhandle.
DELETE OBJECT L-bhandle.

 

[Matias Borra]

I've found another solution. I don't know if it's better but I think it could be easier.

FOR EACH Relacion_Tabla NO-LOCK:
RUN mostrar.p RTA_Nombre_Tabla RTA_Campo_descripcion.
END.


Program mostrar.p

FOR EACH {1}:
DISP {1}.{2}.
END.

Thanks anyway for your cooperation.

Matias.

 

[TomBascom]

There are a couple of downsides to that:

1) You're compiling on the fly -- that is going to cost you some overhead.

2) You're compiling on the fly -- so anyone who runs it needs to have a license that allows them to compile code. In a lot of production environments the users won't have such a license.

3) You're compiling on the fly -- parsing of arguments (the {1} and so forth) can lead to some "entertaining" debugging.

You really ought to learn about dynamic queries. They are the proper way to do what you're trying to do. Compile on the fly is what we used to do back in the very bad old days when we had no choice.