Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to execute 'NOT IN' clause in Execute SQL processor in nifi

avatar
New Contributor

I am trying to execute the following query using 'ExecuteSQL' processor in Apache nifi.

INSERT INTO SampleDB VALUES (${rno}, '${tno}', '${tval}', '${lotno}', '${datval}') WHERE ${rno} NOT IN (SELECT rno FROM SampleDB);

Here the ${rno} is obtained as flow file attribute.'

Insert should happen only if rno is unique.

 

The following error is obtained on execution:

ExecuteSQL[id=01781107-63a4-1204-8110-6b19db3d5ffc] Unable to execute SQL select query INSERT INTO LimsOnCloud VALUES (1, 'CTG123 ', 'ITM123 ', '123 ', '5 ') WHERE 1 NOT IN (SELECT rno FROM SampleDB); for StandardFlowFileRecord[uuid=93db20b2-5f9f-4521-ac42-11239abb94c2,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1615281573937-132, container=default, section=132], offset=168254, length=152],offset=0,name=098a8ad2-0dc9-4564-a242-8b4855b619b2,size=152] due to Incorrect syntax near the keyword 'WHERE'.; routing to failure: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.

 

Any answers and suggestions would help a lot.

1 REPLY 1

avatar
Master Collaborator

@rakz 
Did you try your SQL-statement interactive?
As far as I know INSERT INTO does not accept WHERE-clause at least not at microsoft sql server.