How to merge two SQL queries in ThingWorx?

[PK_11081141]

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!

[Velkumar]

Hi @PK_11081141 

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

/VR

[PK_11081141]

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?

[Velkumar]

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

[PK_11081141]

I am using Oracle database.
Could you tell me if JOIN statements in ThingWorx are functional?
I'm getting an error when I try to join using the LEFT and RIGHT keywords.

[Velkumar]

Hi @PK_11081141 

I tried below Query with PostgreSQL Connection and it is working fine

select public."testTable1"."customerName", public."testTable2"."orderId" from public."testTable1"
LEFT JOIN public."testTable2" ON public."testTable1"."customerId" = public."testTable2"."customerId"
ORDER BY public."testTable1"."customerName";

Could you please post your query here. Also, error message while executing query.

/VR

[VladimirRosu · Accepted solution]

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.

[vnamboodheri]

Hello @PK_11081141, 

It looks like you have some responses from some community members on your topic. If any of these replies helped you solve your question please mark the appropriate reply as the Accepted Solution. 

Of course, if you have more to share on your issue, please let the Community know so other community members can continue to help you.

Thanks,
Vivek N.
Community Moderation Team.