Issue with SQL parameters in Execute Query

Hello all,
I am trying to build a multiple query process that will determine what needs to go into a work queue. I am having issues in that I need the invoice number to be a variable in the query.
Here’s the query:
"Select Distinct FPAPJH.JHJHNO,
FPAPJH.JHIVNO,
FPAPJH.JHPONO,
FPAPJH.JHIVDT,
FLAPJS.JSIVAM,
FLAPXH.XHSTAT,
FLAPXH.XHECDE,
FPAPJH.JHSDID

FROM FPAPJH
INNER JOIN FLAPJS ON FPAPJH.JHJHNO = FLAPJS.JSJHNO
INNER JOIN FLAPXH ON FPAPJH.JHJHNO = FLAPXH.XHJHNO

Where ((FPAPJH.JHIVNO) LIKE (@QueryInvoiceNumber))
Order By FPAPJH.JHIVDT"

When I use the code above I get the following error:
image

I have tried it the above way, I have tried it with Where ((FPAPJH.JHIVNO) =’@QueryInvoiceNumber’)
I have tried it Where ((FPAPJH.JHIVNO) =@QueryInvoiceNumber)
I am at a loss for how to fix this so it will pull the data.

When I try to make it a string with invoiceNumber as a variable in it, it tells me I am missing a quotes (which I know I am not).

When I pull the query with a specific invoice # in the query, Ex Where ((FPAPJH.JHIVNO) =‘123456’), I am able to pull the data without issue.

Appreciate the help.

How are you including your variable? Why you are using a variable and not just generate your statement with string manipulation? Is that invoice number really a number in your database? If it is you need to use = instead of LIKE.

@bcorrea - I have tried using an assign function to create the string for manipulation as well as writing it in directly, but both of those tell me that I missing a quote, which I know I am not. I have tried using = and Like and neither work with the parameter function. No that is not a real invoice number, but when I used = and the exact invoice number it was able to be pulled.

you can use a string variable with your query and use it on the Input Sql property if you find it easier.

@bcorrea - That is what I have been trying to do and was what the pic in my 2nd post was.

not what i meant… create a variable outside of that activity, dont use the Sql Editor… all of your sql statement will be inside it and not only the invoice…

@bcorrea - tried that too… didn’t work


image

you are not getting what i mean, here:
image
see the variable in there, in my case called inText

@bcorrea - yes I am getting what you mean. looks like I missed a ) at the end