SQL Query with WHERE and IN clause

[Sathishkumar_C]

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.

[VladimirN]

Some example is shown here: https://community.ptc.com/t5/IoT-Tech-Tips/Using-a-Validator-with-a-Service-to-perform-more-advanced/td-p/535319 

[Sathishkumar_C]

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]

you can try like this:

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

[Sathishkumar_C]

Thanks Mukul for the support.

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

[Mukul]

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

[Sathishkumar_C]

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]

change your query syntax like this:

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

Change your input like this:

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

[Sathishkumar_C]

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]

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.

[slangley · Accepted solution]

Hi @Sathishkumar_C.

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

Regards.

--Sharon

[Sathishkumar_C]

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

[slangley]

Hi @Sathishkumar_C.

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

Regards.

--Sharon

[Sathishkumar_C]

Its working with only one value.

Not working with multiple comma separated values.

  

[GuruPrasad1]

Hi Satish 

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