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

Community Tip - You can Bookmark boards, posts or articles that you'd like to access again easily! X

SQL Query with WHERE and IN clause

Sathishkumar_C
17-Peridot

SQL Query with WHERE and IN clause

How to implement SQL query in thingworx as follows,

Query:

SELECT * FROM Customers
WHERE Country NOT IN ('Germany''France''UK');

 

In thingworx, how to pass country names as input parameter.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Sathishkumar_C.

 

Take a look at this post to see if it helps.

 

Regards.

 

--Sharon

 

View solution in original post

14 REPLIES 14

No. Im going to use this query in "Database Thing" as "SQL Query". So, I want pass comma separated values to this query. How can i do with Thinworx SQL query services?

Mukul
13-Aquamarine
(To:Sathishkumar_C)

you can try like this:

select * from user_model where name not in ([[name1]],[[name2]])
Where name1 and name2 are your service inputs.

Thanks Mukul for the support.

 

My case is inputs are dynamic. Based on multiple selection in drop-down, I want query using IN.

Mukul
13-Aquamarine
(To:Sathishkumar_C)

You can try this approach, take all the selection as an infotable, push each value of that infotable column(country) into an array and make the array output something like ('Germany','France'); pass this String array as an input to SQL query service wherein put input like - select * from customers where country not in <<input>>;

Thanks for the support.

 

It seems, not working for me.

 

SQL query: testSQL

input (string)

select * from table where countrycode in input;

 

JavaScript Service:

var input = [530, 531];
let result = me.Test({
input: input /* STRING */
});

 

I am getting following error message.

Message :: Execute Update failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ']'. - See Script Error Log for more details.

 

Let me know anything i missed here.

Mukul
13-Aquamarine
(To:Sathishkumar_C)

change your query syntax like this:

select * from table where countrycode in <<input>>;

 

Change your input like this:

 

var input = ('530','531')

I tried the same.

 

Input basetype is String.

 

select * from table where countrycode in <<input>>;

input = ('530','531');

 

Getting following error:

Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

Mukul
13-Aquamarine
(To:Sathishkumar_C)

I think SQL server is not accepting it, I have postgres in my use case and it works fine like below. Let me test this with mssql, will get back to you.

 

Mukul_0-1638181534376.png

 

Hi @Sathishkumar_C.

 

Take a look at this post to see if it helps.

 

Regards.

 

--Sharon

 

Thanks for you support. Let me test in my environment and confirm.

slangley
23-Emerald II
(To:slangley)

Hi @Sathishkumar_C.

 

Were you able to complete your testing?  What were the results?

 

Regards.

 

--Sharon

Filter Working.PNG

Its working with only one value.

 

Filter Error.PNG

Not working with multiple comma separated values.

  

Hi Satish 

try putting the input like  (<<filter>>) and not like([[filter]]) this is mentioned in the link provided by Sharon.

Top Tags