Concatenating data from two REST connections

[schmidtj]

Hi,

i can't make it work to concatenate data from two REST connections.

Both my REST connections are valid and return a valid JSON response

The problem ist the following:

The data of the second connection ist not beeing concatenated to the data table before.

It doesnt matter here which REST connection comes first in the order of the script.

It is always the data of the first connection which is loaded.

Maybe somebody can tell me what i'm doing wrong?

This is my script:

-----------------------------------------------------------------------------------------------------------------------

// Data from first REST connection

LIB CONNECT TO 'REST connection 1';

RestConnectorMasterTable:

SQL SELECT

     "__KEY_root",

    "field1",

    ...

FROM JSON (wrap on) "root" PK "__KEY_root";

[DATA]:

LOAD

     ...   

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

// Data from second REST connection

LIB CONNECT TO 'REST connection 2';

RestConnectorMasterTable:

SQL SELECT

     "__KEY_root",

    ...

FROM JSON (wrap on) "root" PK "__KEY_root";

concatenate

LOAD

     ...       

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

[agigliotti]

could you show your data model?

[schmidtj]

Hi Andrea,

as i understand the concatenation the data model is not relevant, ist it?

I thought the data ist just added to the first table?

[agigliotti]

to understand better your problem I'd need to see the whole script at least, not partial as shown above.

[schmidtj]

Well, i just commented out the columns to have a better overview.

This is my complete script:

---------------------------------------------------

LIB CONNECT TO 'ES_proxy_izchecker - BASISDATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "checkergebnis",

    "dateiname",

    "dateityp",

    "status",

    "system",

    "time_id"

FROM JSON (wrap on) "root" PK "__KEY_root";

[DATEN]:

LOAD

     [checkvorgang_id] AS [Checkvorgang-ID],

     [auftrag_id] AS Auftrag_ID,

     [benutzer_id] AS Benutzer_ID,

     [checkergebnis] AS [Checkergebnis],

     [dateiname] AS [Dateiname],

     [dateityp] AS [Dateityp],

     [status] AS [Status],

    [system] AS [Laufsystem],

    [time_id] AS [time_id],

    date(

      date#([time_id], 'YYYYMMDDhhmmss'),

      'DD.MM.YYYY - hh:mm:ss'

    ) AS Timstamp

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LIB CONNECT TO 'ES_proxy_izchecker - TXT_DATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "dateiname",

    "dateityp",  

    "system",

    "time_id",

    "HANDLE",

    "BLOCKNAME",

    "MAIN_INFO",

    "DEVICE_NUMBER",

    "GROUP",

    "ADD_INFO",

    "ADD_INFO2",

    "COUNTER",

    "ROOM_NUMBER",

    "AREA",

    "CABLES"

FROM JSON (wrap on) "root" PK "__KEY_root";

concatenate

LOAD

[checkvorgang_id] AS [Checkvorgang-ID],

    [auftrag_id] AS Auftrag_ID,

     [benutzer_id] AS Benutzer_ID,

     [dateiname] AS [Dateiname],

     [dateityp] AS [Dateityp],

    [system] AS [Laufsystem],

    [time_id] AS [time_id],

    date(

      date#([time_id], 'YYYYMMDDhhmmss'),

      'DD.MM.YYYY - hh:mm:ss'

    ) AS Timstamp,   

    [HANDLE] AS handle,

    [BLOCKNAME],

    [MAIN_INFO],

    [DEVICE_NUMBER],

    [GROUP],

    [ADD_INFO],

    [ADD_INFO2],

    [COUNTER],

    [ROOM_NUMBER],

    [AREA],

    [CABLES]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

[agigliotti]

if you want to concatenate the two tables , do as below:

LIB CONNECT TO 'ES_proxy_izchecker - BASISDATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "checkergebnis",

    "dateiname",

    "dateityp",

    "status",

    "system",

    "time_id"

FROM JSON (wrap on) "root" PK "__KEY_root";

[DATEN]:

LOAD

     [checkvorgang_id] AS [Checkvorgang-ID],

     [auftrag_id] AS Auftrag_ID,

     [benutzer_id] AS Benutzer_ID,

     [checkergebnis] AS [Checkergebnis],

     [dateiname] AS [Dateiname],

     [dateityp] AS [Dateityp],

     [status] AS [Status],

    [system] AS [Laufsystem],

    [time_id] AS [time_id],

    date(

      date#([time_id], 'YYYYMMDDhhmmss'),

      'DD.MM.YYYY - hh:mm:ss'

    ) AS Timstamp

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LIB CONNECT TO 'ES_proxy_izchecker - TXT_DATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "dateiname",

    "dateityp",

    "system",

    "time_id",

    "HANDLE",

    "BLOCKNAME",

    "MAIN_INFO",

    "DEVICE_NUMBER",

    "GROUP",

    "ADD_INFO",

    "ADD_INFO2",

    "COUNTER",

    "ROOM_NUMBER",

    "AREA",

    "CABLES"

FROM JSON (wrap on) "root" PK "__KEY_root";

concatenate

LOAD * resident [DATEN];

drop table DATEN;

[schmidtj]

Hi Andrea,

thanks for your reply.

I tried that and i get an error.

I dont quite understand the logic either though.

You basically change the tabele i am concatenating from in the second connection, right?

'RESIDENT [DATEN];'

Why do it want to concatenate data from resident [DATEN]?

I loaded the data from the second connection in RestConnectorMasterTable?

[agigliotti]

yes the script concatenate your second connection table with the first one.

what error do you get ?

[schmidtj]

The error is:

Field 'checkvorgang_id' not found

[agigliotti]

related to the first or second sql select?