Skip to main content
A
ashishtamhane10-Marble
10-Marble
June 7, 2024
Question

Relational join query in ThingWorx

  • June 7, 2024
  • 2 replies
  • 1861 views

Is there something equivalent to a simple SQL Join query as below in ThingWorx:

 

SELECT T1.Name, T2.Name, T2.Date FROM

Table1 T1 INNER JOIN Table2 ON T1.Name = T2.Nname

2 replies

V
VladimirRosu_11662719-Tanzanite
19-Tanzanite
June 7, 2024

Are you asking about this capability for an infotable? Or are you asking how to execute SQL commands to an external SQL table from within ThingWorx?

A
ashishtamhane10-MarbleAuthor
10-Marble
June 7, 2024

Capability for an infotable is desired which does all the querying in a single statement as opposed to:

 

1. SELECT T1.Name FROM Table1

2. SELECT T2.Name FROM Table2

3. Perform join query on above 2 tables

 

ie we would like to reduce the Select statement cost as it will be expensive in Production.

V
VladimirRosu_11662719-Tanzanite
19-Tanzanite
June 7, 2024

Intersect is the snippet to use: https://www.ptc.com/en/support/article/CS340098?source=search

But...one very important point: the amount of data that you will retrieve in the beginning. I understand that SELECT could be expensive, but if you're querying (initially) millions of rows that then you keep as infotables, then you'll have an impact on memory...

mstarnaud
mstarnaud16-Pearl
16-Pearl
June 7, 2024

Hi Ashish

 

I'm not an expert on this, but I can share an example that my team has built.

There are 2 tables in our example : Attributes, and AttributeValues. They each have their own DataShape. The AttributeValue table includes column AttributeUid, which joins on Attributes' Uid. So here is what they did for a Select : 

 

let MyFilter = {
select: [{dataShapeName: 'CompanyNameHere.AttributeValue', fieldName: 'Value'},
{dataShapeName: 'CompanyNameHere.AttributeValue',fieldName: 'AttributeUid'}],
filters: {
filters: [{dataShapeName: 'CompanyNameHere.AttributeValue',fieldName: 'IsProcessed',type: 'EQ',value: 0}]
},
joins: [{type: 'INNER', sourceDataShapeName: 'CompanyNameHere.AttributeValue', sourceFieldName: 'AttributeUid', targetDataShapeName: 'CompanyNameHere.Attribute', targetFieldName: 'Uid'}]
};

result = me.GetManyOf({Filter: MyFilter, DataShapeName: 'CompanyNameHere.AttributeValue'});

 

I believe this is the equivalent of this query : 

 

SELECT av.Value, av.AttributeUid
FROM AttributeValues av 
JOIN Attributes a ON a.Uid = av.AttributeUid
WHERE av.IsProcessed = 0

 

Let us know if that helps.

Terms & ConditionsCookie settingsAccessibility statement