I wish I could find the source, but I once read that since there's no link between the two tables, the subselect would get evaluated once in the example I gave you. I believe that was the wording. Thus behaving as if you coded it as word1 in (field1, field2, field3, etc).
I'll look some more. Please if I'm not correct please speak up. We're trying to setup a class for sql beginnings and I would hate to mislead my teammates.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bryce Martin
Sent: Monday, January 10, 2011 3:58 PM
To: RPG programming on the IBM i / System i
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be working....INclause
Won't the multiple select statement have a negative impact on performance
compared to a list of predetermined values?
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
"Schutte, Michael D" <Michael_Schutte@xxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 03:36 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....INclause
Change this to...
Select Cast(rncust As Numeric(6,0)),
Cast(rnseq As Numeric(4,0)),
Cast(rntype As Numeric(2,0)),
rnword1, rnword2, rnword3, rnword4, rnword5
From rcm002pf
Where rnword1 in (select WorkKey1 from WorkTable1)
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Morgan, Paul
Sent: Monday, January 10, 2011 2:37 PM
To: RPG programming on the IBM i / System i
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working....INclause
Martin,
Modifying your original example:
Delete from WorkTable1;
Insert into WorkTable1 (WorkKey1) values( :word1 );
Insert into WorkTable1 (WorkKey1) values( :word2 );
Insert into WorkTable1 (WorkKey1) values( :word3 );
Select Cast(rncust As Numeric(6,0)),
Cast(rnseq As Numeric(4,0)),
Cast(rntype As Numeric(2,0)),
rnword1, rnword2, rnword3, rnword4, rnword5
From rcm002pf
Where ( Exists( select *
from WorkTable1
where WorkKey1 = rnword1) or
( ( select count(*)
from WorkTable1 ) = 0 )
and rntype = (case when :type<> '' then :NumType else rntype End)
Order by rncust, rnseq;
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Bryce Martin
Sent: Monday, January 10, 2011 2:11 PM
To: RPG programming on the IBM i / System i
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working....INclause
I understand your suggestion, just not how it fits with what I'm trying to
do.
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
"Morgan, Paul" <Paul.Morgan@xxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 01:48 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....
INclause
Martin,
Have you considered using some work tables? Insert the values for the
selection as rows in the table then use an EXISTS clause in the where
statement to select rows from the primary table that are in the work
table. Clear and insert values in the work tables before running the SQL
instead of preparing a new SQL statement.
Paul
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Bryce Martin
Sent: Monday, January 10, 2011 1:30 PM
To: RPG programming on the IBM i / System i
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working.... INclause
I definitely understand the concern you raise, but this statement is
behind a few layers of logic and doesn't actually return results a calling
program. The results are used as park of a ranking algorithm with other
criteria and goes through a weighting algorithm that returns only the top
20 results. So SQL injection isn't a worry.
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
"Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 12:56 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
Beware of SQL Injection attacks when you do this. If NameString were to
contain something like "space' and ('1' = '1" you would get every record
in the database. May or may not be an issue in this application since the
user may be authorized to all the records, but just saying that using
prepared statements with appropriate parameter markers is far safer than
allowing the user to enter SQL source.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
From: "Schutte, Michael D" <Michael_Schutte@xxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date: 01/10/2011 08:23 AM
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working.... IN clause
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
Another option is to build the entire select statement in a string
variable. Prepare it and then declare your cursor over the prepared
statement.
For example.
STRSQL = 'SELECT ' + ColumnsSQL + ' FROM DCR_PIVOT +
WHERE RNWORD1 in (' + %Trim(NameString) + ') +
ORDER BY ' + %TRIM(SortBy);
Exec SQL PREPARE S1 FROM :STRSQL;
Exec SQL DECLARE C1 CURSOR FOR S1;
Exec SQL OPEN C1;
Exec SQL Fetch C1 Into :ColumnsDS
DoW SQLCOD <> 100 And SQLCOD >= 0;
...
Exec SQL Fetch C1 Into :ColumnsDS
EndDo;
I'm assuming RNWORD1 is character so you will need to make sure that you
have the single quotes around each name in your string.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Bryce Martin
Sent: Friday, January 07, 2011 5:08 PM
To: RPG programming on the IBM i / System i
Subject: Using host variables in SQLRPGLE doesn't seem to be working....
IN clause
I might be trying to be too fancy here, but I thought that maybe this
would work.
I have an array of values (words) that I need to search a particular field
for in a file.... So I thought that this might work...
Exec SQL
Declare NameSet Cursor For
Select Cast(rncust As Numeric(6,0)),
Cast(rnseq As Numeric(4,0)),
Cast(rntype As Numeric(2,0)),
rnword1, rnword2, rnword3, rnword4, rnword5
From rcm002pf
Where rnword1 in(:NameString)
and rntype = (case when :type<>'' then :NumType else rntype
End)
Order by rncust, rnseq;
I build NameString from the array values. Its a character string. Its
value is like... 'word1','word2','word3'
My thinking is that NameString would just plop right inside the IN()
clause and act as the list to check. But I keep getting and sql 100 error
(no results). If I put this into my favorite sql tool it works fine. So
I know that its something to do with the SQL. I thought this would be
cleaner than doing a Prepare, but maybe I'll have to do it anyway.
Any thoughts????
Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
--- 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
