Advanced query using IN won't work

Hi I have an advanced query using IN.

I have set the query parameter to expand inline.

The query parameter is "'BG10'" or with multiple: "'BG10,','PL02'" (all encapsulated in single quotes).

Te query is below.

Where companyCodes is the inserted parameter.

Running a test when using "'BG10'" works and returns results.

However when running the query in the flow of the app it returns the following error:

Error in advanced query GetCompanyMasterDataByCCode in OnTickCompanyCode in GLAccountReconciliations in MainFlow in APP(SELECT {CompanyMasterData}.* FROM {CompanyMasterData} WHERE {CompanyMasterData}.[Code] IN ( @CompanyCodes2 )): Incorrect syntax near 'BG10'.

SELECT {CompanyMasterData}.* FROM {CompanyMasterData} WHERE {CompanyMasterData}.[Code] IN (@CompanyCodes)

Hi Johnny,

Shouldn't "'BG10,','PL02'" be " 'BG10', 'PL02' " (I added spaces to make it easier to see, but you can ignore them)

Hi!, 

It should work, if CompanyCodes2  has his Expand Inline property set to Yes. 

Try to DEBUG your code to see exactly what you are sending to the Advanced query, copy the value from the debug screen and do the test on the advanced query again but with that value.

Some times this help to figure out what is happening. 

Regards 

Graça

Carlos Ribeiro da Fonseca wrote:

Hi Johnny,

Shouldn't "'BG10,','PL02'" be " 'BG10', 'PL02' " (I added spaces to make it easier to see, but you can ignore them)

That is what I got,

 The first double comma should not be there and isn't in my code. SO I have the same as you suggested.


Solution

I discovered what was the culprit. i had EncodeSQL on the input parameter. This removes the outer single quotes. I did not notice it in the first place. Thanks for the efforts! Maria and Carlos.

Solution