Skip to main content
P
PK_110811417-Bedrock
7-Bedrock
July 9, 2024
Solved

How to merge two SQL queries in ThingWorx?

  • July 9, 2024
  • 1 reply
  • 2153 views

I'm working on integrating data from two separate SQL queries within ThingWorx. Here's the scenario:

  1. First Query: Fetches data from table based on a specific value.

  2. Second Query: Utilizes data from the first query and joins with new two columns.

I need guidance on how to effectively merge these two SQL queries within ThingWorx. Specifically, how can I pass results from the first query as parameters or dynamically construct the second query using data fetched from the first query?

Any examples or suggestions would be greatly appreciated!

Best answer by VladimirRosu_116627

HI @PK_11081141 

 

I don't see the specific error message, and in absence of context it could also be that JDBC driver for the database you're using has a bug. I remember an Amazon JDBC driver for AWS Athena 4 years ago that had significant bugs (did not support parametrized queries and the UNION keyword) that basically made it unusable in production (and that was in addition to the fact that Athena itself did not support stored procedures and neither declaring variables via DECLARE keyword).

In your case it's not about whether ThingWorx supports JOIN, instead it's about whether you're using JOIN correctly or whether the JDBC driver supports JOIN. Based on my previous experience I suggest running the query in an external query tool first, to make sure there's nothing TW specific that might impact your workload. If you have a query that executes correctly, then you can just paste it in TW and it should execute here correctly as well.

 

However, as a kind advice for the future, aimed at helping others users understand your situation, whenever possible, please remember to be specific and share the specific error messages you're getting with as many details as possible, database names and versions etc. The more info, the better chances you'll have on getting help.

1 reply

V
19-Tanzanite
Velkumar19-Tanzanite
19-Tanzanite
July 9, 2024

Hi @PK_11081141 

 

Please check this link - sql - How do I use results of first query in second query? - Stack Overflow

 

/VR

P
PK_110811417-BedrockAuthor
7-Bedrock
July 9, 2024

I guess ThingWorx SQL queries are different, queries are not getting compiled 
It's also not working by using INTO keyword so that I can store first query result into the new table.

Do we have any reference for how SQL queries are written?

V
19-Tanzanite
Velkumar19-Tanzanite
19-Tanzanite
July 9, 2024

Hi @PK_11081141 

 

I just ran below query from Thingworx and it is working properly.

SELECT * from public."TableName"
WHERE "ThingName" IN 
(SELECT "ThingName" from public."TableName" WHERE "ThingName" IS NOT NULL Limit 10);

 

May I know which Database connected to Thingworx

 

/VR

 

Terms & ConditionsCookie settingsAccessibility statement