passing like operator in Sql query

[svisveswaraiya]

Hi,

In my sql query I am trying to search words in column having letters that I search for. I am using LIKE operation to perform this but it doesn't work. It works fine in sql server but in TW it shows error.

TW:

select * from
(SELECT DISTINCT STUFF(WORK_ORDER.order_number,1,8,'') as JOBNum FROM WORK_ORDER) t1

WHERE t1.JOBNum LIKE '%'[[input]]'%'

OUTPUT ERROR: Unable to Invoke Service SequencedProductionOrder_JobNumMatch on NAVISTAR_DB_CONFIG : Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

SQLSERVER:

select * from
(SELECT DISTINCT STUFF(WORK_ORDER.order_number,1,8,'') as JOBNum FROM WORK_ORDER) t1

WHERE t1.JOBNum LIKE '%234%'

**WORKS FINE**

How can I resolve this and use the operator in TW?

Thanks in advance,

Shalini V.

[tmisner]

Hello Shalini,

Thank you reaching out on the PTC Developer Community.

I proceeded to run some tests using a query similar to yours which includes a subquery along with a LIKE statement.

My initial tests worked correctly in that the expected information was returned from the database. This configuration however did not include a passed in parameter value.

When I appended a pass in value I was able to duplicate the "Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'." error in tests where quotes were not used around the LIKE criteria.

When using quotes I get "The Index 1 is out of range" error. This is likely due to the double square brackets which are required around the parameter. At this time it seems this may be a limitation with the database connector.

Please create a case with Technical Support via the PTC Support Portal. So we can look into this further and confirm if this is indeed a limitation.

Regards,

Tyler Misner

ThingWorx Technical Support Engineer