Well, it would, except I want whole word matches based on each comma
separated value in NameString. I don't want any partials. So if
rnword='hospital' and the word in NameString is 'hospitality' I don't want
it to find a match.
I got it to work, I just had build the statement and use a Prepare.
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 09:09 AM
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
AW: Using host variables in SQLRPGLE doesn't seem to be working.... IN
clause
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...
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--- This message (including any attachments) is intended only for the use
of
the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential, and
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us and
destroy
this message immediately. ---
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--- This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us and destroy this message immediately. ---
midrange.com
