cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

How to merge two SQL queries in ThingWorx?

PK_11081141
4-Participant

How to merge two SQL queries in ThingWorx?

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!

ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

7 REPLIES 7
Velkumar
19-Tanzanite
(To:PK_11081141)

PK_11081141
4-Participant
(To:Velkumar)

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
19-Tanzanite
(To:PK_11081141)

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
4-Participant
(To:Velkumar)

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
19-Tanzanite
(To:PK_11081141)

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";

 

 

Velkumar_1-1720676016412.png

 

 

 

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

 

/VR

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.

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.

Announcements


Top Tags