× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi,

the following solution should work with static SQL:

Exec SQL Declare C1 Cursor for
Select *
From YourFile
Where :NameSting like '%' concat Trim(rnword) concat '%'
...

May be performance is not the best

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Bryce Martin
Gesendet: Monday, 10. January 2011 14:34
An: RPG programming on the IBM i / System i
Betreff: Re: Using host variables in SQLRPGLE doesn't seem to be working....
IN clause

Thanks for the explanation Scott.

I guess the real point here is the the commas are part of the sql
statement that must be precompiled. I actualy have 5 different columns I
need to do this with so I was trying to be a bit more succinct and use
just 1 host variable 5 times instead of 5 host variables 5 times. Plus, I
only want to include the host variable if it isn't equal to *blanks, so I
guess i'm just going to have to build and prepare the statement.

Thanks for the clear explanation. Good way to start a Monday, learning
learning learning.


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777



Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/07/2011 05:33 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
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...

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.