How to joined query two data table?

[fzhong]

Hi,

     is it possible to query two Data Table with joined field in thingworx? just like what we do in relational database sql. Thanks!

[CarlesColl]

Search for Intersect Snippet

[ankigupta]

Hi Feng Zhang​,

Basically; we read the DataTable in two Infotables and apply InfoTable functions. You have option to apply Intersect (suggested by Carles) or Union (If the Datashape of both DataTables is same) as per your requirement.

[fzhong]

Carles ​&  Ankit Gupta

     Thank you for answer, but if these are two big Data Table,  will it be performance issue to read all data into Infotables?

[ankigupta]

Hi Feng Zhang​,

Yes, there could be performance issues when reading very large amount of data to infotables. It will vary based on the system configuration and network connectivity etc.

[fzhong]

So I think I should seek for alternate solution because some of my Data Tables has more than 1 million rows.

[CarlesColl]

The DataTable it's a standard TW DataTable? if that's the case you are out of luck, you shouldn't use TW DataTables bigger than few hundred and thousand rows, you should go for an external storage database for this kind of DataTables and use embedded SQL support ( JDBC ) that provides TW.

[fzhong]

Thank you Carles, is there any limitation to store data in external storage database? For example,  can we share  things stored  externally between federation instances?

[CarlesColl · Accepted solution]

I've never did, but you shouldn't have any problem to have two JDBC connections to the same Database from different servers.