mikelcid
New Member
Hi all!
I have a problem when building a dataset. What I am doing is joining some tables and filtering the data from the first one to give to a DevExpress control that shows all the data available from that dataset. The problem is that sometimes I join table A to table B and it works fine but when doing a joining from table B to table A it seems that it does not work fine. I am really trying hard to find where is the problem but I don't know if it is in the ADD-RELATION() clause or where can it be. The code is the following one:
The tt_lininf passed to this class contains the names of the tables, the fields used to join them and the conditions that have to be applied it them.
I would be very grateful if somebody can help me as it really make frustrated.
PD: I am using Progress 10.2B.
I have a problem when building a dataset. What I am doing is joining some tables and filtering the data from the first one to give to a DevExpress control that shows all the data available from that dataset. The problem is that sometimes I join table A to table B and it works fine but when doing a joining from table B to table A it seems that it does not work fine. I am really trying hard to find where is the problem but I don't know if it is in the ADD-RELATION() clause or where can it be. The code is the following one:
Code:
USING Progress.Lang.*.USING DevExpress.XtraReports.UI.*.
CLASS base_informes.crearDataSetDinamico:
DEFINE PRIVATE VARIABLE bindingSource1 AS Progress.Data.BindingSource NO-UNDO.
DEFINE PRIVATE TEMP-TABLE tt_lininf LIKE gi_lininf.
DEFINE VARIABLE iEntry AS INTEGER NO-UNDO.
DEFINE VARIABLE hDataSource AS HANDLE NO-UNDO.
DEFINE VARIABLE phDataSet AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferTemporal AS HANDLE NO-UNDO.
DEFINE VARIABLE pcSourceKeys AS CHARACTER NO-UNDO.
DEFINE VARIABLE pcKeyValue AS CHARACTER NO-UNDO.
DEFINE VARIABLE pcBuffers AS CHARACTER NO-UNDO.
DEFINE VARIABLE pcSources AS CHARACTER NO-UNDO.
DEFINE VARIABLE pcFields AS CHARACTER NO-UNDO.
DEFINE VARIABLE pcTablas AS CHARACTER NO-UNDO.
DEFINE VARIABLE tabla AS HANDLE NO-UNDO.
DEFINE VARIABLE tt_bd AS CHARACTER NO-UNDO.
DEFINE VARIABLE campo1 AS CHARACTER NO-UNDO.
DEFINE VARIABLE campo2 AS CHARACTER NO-UNDO.
DEFINE VARIABLE tabla1 AS CHARACTER NO-UNDO.
DEFINE VARIABLE tabla2 AS CHARACTER NO-UNDO.
DEFINE VARIABLE indice AS CHARACTER NO-UNDO.
CONSTRUCTOR PUBLIC crearDataSetDinamico (INPUT TABLE tt_lininf, INPUT ordenacion AS CHARACTER,
INPUT lp_batch AS LOGICAL,
OUTPUT bindingSource1 AS Progress.Data.BindingSource):
FOR EACH tt_lininf NO-LOCK BY tt_lininf.secuencial_lin:
tt_bd = tt_lininf.tabla_lin.
CREATE TEMP-TABLE tabla.
tabla:CREATE-LIKE(tt_bd).
tabla:TEMP-TABLE-PREPARE(tt_bd).
IF pcBuffers = "" THEN
pcBuffers = STRING(tabla).
ELSE
pcBuffers = pcBuffers + "," + STRING(tabla).
IF pcSources = "" THEN
pcSources = tt_lininf.tabla_lin.
ELSE
pcSources = pcSources + "," + tt_lininf.tabla_lin.
IF tt_lininf.relacion_lin <> ? AND tt_lininf.relacion_lin <> "" THEN DO:
campo1 = ENTRY (1, tt_lininf.relacion_lin, "=").
tabla1 = TRIM(ENTRY (1, campo1, ".")).
campo1 = TRIM(ENTRY (2, campo1, ".")).
campo2 = ENTRY (2, tt_lininf.relacion_lin, "=").
tabla2 = TRIM(ENTRY (1, campo2, ".")).
campo2 = TRIM(ENTRY (2, campo2, ".")).
DO iEntry = 1 TO NUM-ENTRIES(pcSources):
IF ENTRY(iEntry,pcSources,",") = tabla1 THEN
tabla1 = STRING(iEntry).
IF ENTRY(iEntry,pcSources,",") = tabla2 THEN
tabla2 = STRING(iEntry).
END.
IF INT(tabla1) < INT(tabla2) THEN DO:
IF pcFields = "" THEN DO:
pcFields = campo1 + "," + campo2.
pcTablas = tabla1 + "," + tabla2.
END.
ELSE DO:
pcFields = pcFields + "|" + campo1 + "," + campo2.
pcTablas = pcTablas + "|" + tabla1 + "," + tabla2.
END.
END.
ELSE DO:
IF pcFields = "" THEN DO:
pcFields = campo2 + "," + campo1.
pcTablas = tabla2 + "," + tabla1.
END.
ELSE DO:
pcFields = pcFields + "|" + campo2 + "," + campo1.
pcTablas = pcTablas + "|" + tabla2 + "," + tabla1.
END.
END.
END.
END.
/* Crear sentencia para la query */
DEFINE VARIABLE sentencia AS CHARACTER NO-UNDO INITIAL "".
DEFINE VARIABLE iw_contador AS INTEGER NO-UNDO.
FOR EACH tt_lininf NO-LOCK BY tt_lininf.secuencial_lin:
IF tt_lininf.condicion_lin <> "" AND tt_lininf.condicion_lin <> ? THEN DO:
IF sentencia = "" THEN
sentencia = " WHERE " + tt_lininf.condicion_lin.
ELSE
sentencia = sentencia + "|" + " WHERE " + tt_lininf.condicion_lin.
END.
ELSE DO:
IF sentencia = "" THEN
sentencia = "*".
ELSE
sentencia = sentencia + "|" + "*".
END.
END.
CREATE DATASET phDataSet.
DO iEntry = 1 TO NUM-ENTRIES(pcBuffers):
phDataSet:ADD-BUFFER(WIDGET-HANDLE(ENTRY(iEntry, pcBuffers))).
END.
DEFINE VARIABLE iw_tablas AS INTEGER NO-UNDO.
iw_tablas = NUM-ENTRIES(pcSources, ",").
DO iEntry = 1 TO iw_tablas:
FOR EACH _File WHERE _File._File-Name = ENTRY(iEntry,pcSources,",") NO-LOCK,
EACH _Index WHERE _Index._File-recid = RECID(_File) NO-LOCK:
IF _Index._Unique = TRUE THEN DO:
FOR EACH _Index-Field WHERE _Index-Field._Index-recid = RECID(_Index) NO-LOCK,
FIRST _Field OF _INdex-Field NO-LOCK:
IF pcSourceKeys = "" THEN
pcSourceKeys = _Field._Field-Name.
ELSE
pcSourceKeys = pcSourceKeys + "," + _Field._Field-Name.
END.
END.
END.
END.
DEFINE VARIABLE tablas AS CHARACTER NO-UNDO.
DEFINE VARIABLE iw_relaciones AS INTEGER NO-UNDO.
DEFINE VARIABLE t1 AS INTEGER NO-UNDO.
DEFINE VARIABLE t2 AS INTEGER NO-UNDO.
iw_relaciones = NUM-ENTRIES(pcFields, "|").
DO iEntry = 1 TO iw_relaciones:
tablas = ENTRY(iEntry,pcTablas,"|").
t1 = INT(ENTRY(1,tablas,",")).
t2 = INT(ENTRY(2,tablas,",")).
phDataSet:ADD-RELATION(phDataSet:GET-BUFFER-HANDLE(t1),
phDataSet:GET-BUFFER-HANDLE(t2),
ENTRY(iEntry,pcFields,"|")).
END.
DO iEntry = 1 TO NUM-ENTRIES(pcSources):
CREATE DATA-SOURCE hDataSource.
CREATE BUFFER hBuffer FOR TABLE ENTRY(iEntry, pcSources).
hDataSource:ADD-SOURCE-BUFFER(hBuffer, ENTRY(iEntry,pcSourceKeys)).
IF lp_batch THEN
phDataSet:GET-BUFFER-HANDLE(iEntry):BATCH-SIZE = 100.
phDataSet:GET-BUFFER-HANDLE(iEntry):ATTACH-DATA-SOURCE(hDataSource).
IF iEntry = 1 THEN DO:
CREATE QUERY hQuery.
hQuery:ADD-BUFFER(hBuffer).
DEFINE VARIABLE sentenciaQuery AS CHARACTER NO-UNDO.
IF ENTRY(iEntry,sentencia,"|") <> "*" THEN DO:
sentenciaQuery = "FOR EACH " + ENTRY(iEntry, pcSources) + ENTRY(iEntry,sentencia,"|") + " NO-LOCK ".
hQuery:QUERY-PREPARE(sentenciaQuery).
END.
ELSE DO:
sentenciaQuery = "FOR EACH " + ENTRY(iEntry, pcSources) + " NO-LOCK ".
hQuery:QUERY-PREPARE(sentenciaQuery).
END.
hDataSource:QUERY = hQuery.
END.
END.
phDataSet:FILL().
DELETE OBJECT hQuery.
DO iEntry = 1 TO phDataSet:NUM-BUFFERS:
hBuffer = phDataSet:GET-BUFFER-HANDLE(iEntry).
DELETE OBJECT hBuffer:DATA-SOURCE.
END.
bindingSource1 = NEW Progress.Data.BindingSource(phDataSet).
END METHOD.
END CLASS.The tt_lininf passed to this class contains the names of the tables, the fields used to join them and the conditions that have to be applied it them.
I would be very grateful if somebody can help me as it really make frustrated.
PD: I am using Progress 10.2B.