Re: Using host variables in SQLRPGLE doesn't seem to be working.... IN clause

Hi Bryce,

On 1/7/2011 4:08 PM, Bryce Martin wrote:
...snip...

Where rnword1 in(:NameString)

...snip...

I build NameString from the array values. Its a character string. Its
value is like... 'word1','word2','word3'

Nope, that won't work.

To understand this, make a distinction between the "source code" for your SQL statement, and the actual code that gets executed.

We all know that you want the finished SQL statement to look like this:

Where rnword1 = ('word1', 'word2', 'word3')

We know that's what you *want*... And if you physically insert the contents of your string into the SQL statement, it would indeed look like that. but that's source code! the SQL precompiler is converting that statement into something else... it doesn't remain in your program in that form after it's been compiled!

So if the precompiler sees this:

Where rnword1 in(:Word1, :Word2, :Word3)

It generates code that does something like (pseudo-code):

1) Move contents of Word1 into first choice
2) Move contents of Word2 into second choice
3) Move contents of Word3 into third choice
4) Call database API

And that'll work just fine. but, following the same logic, the code you've written looks like this:

Where rnword1 in(:NameString)

1) Move contents of NameString into first choice
2) Call database API

It doesn't know that NameString contains more SQL statement syntax that it should interpret it as part of the "source code!" It doesn't try to "compile" the data in your NameString at run-time! It, therefore, can't contain anything that's part of the SQL statement syntax!

However, you certainly can do this:

Where rnword1 in(:Word1, :Word2, :Word3)

And you can set Word1, Word2 and Word3 to the same value if you only want it to find one value in the file, instead of 3 different ones...