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



Sharon,

You're mixing and matching syntax for dynamic and static SQL..

--static SQL (always safe)---
exec SQL
declare C1 cursor for
select *
from ORDERR
where OSTA00
not in('DEL','RCP','XXX')
and :#@HAST in ('', HAST00)
<...>

--unsafe dynamic SQL---
wSqlStmt = 'Select * ' +
'from ORDERR '+
'where OSTA00 '+
'not in(''DEL'',''RCP'',''XXX'') '+
'and ' + '''' + #@HAST +''''+ ' in ('''''', HAST00)' +

---safe dynamic SQL---
wSqlStmt = 'Select * ' +
'from ORDERR '+
'where OSTA00 '+
'not in(''DEL'',''RCP'',''XXX'') '+
'and ? in ('''''', HAST00) ' +

Notice the difference in the quoting of 'DEL' and the use of the
variable #@HAST.

There is nothing in your statement that requires the use of dynamic SQL....
Therefore you should use static SQL. It's safer and performs better.

HTH,
Charles


On Wed, Aug 3, 2011 at 8:41 AM, Sharon Strippoli
<sharonstrippoli@xxxxxxxxxxxxxxxxx> wrote:
Hi Michael, trying to get this to work but getting an error, wanted to see
if it was obvious to you.

Code:
0272.80 C     CreateSQLNew  BegSR

0272.90
0273.00 C/Exec SQL
0273.10 C+ Declare NewCsr Cursor for Newstmt
0273.20 C/End-Exec

0273.00

0273.10 C                   If        NewCsrOpen

0273.20 C/Exec SQL

0273.30 C+ Close NewCsr

0273.40 C/End-Exec

0273.50 C                   Eval      NewCsrOpen = *Off

0273.60 C                   EndIf

0273.70  *

0273.80 C* Case statements to determine SQL

0273.90

0274.00 C                   Eval      Newstmt = 'Select *  +

0274.10 C                                        from ORDERR +

0274.90 C                                where OSTA00 +

0275.00 C                                 not in(''DEL'',''RCP'',''XXX'') +

0275.10 C                                and :#@HAST in ('', HAST00) +

0275.20 C                                 or :#@HAS2 in ('', HAST00) +

0275.30 C                                and :#@HAWB in ('', HAWB00) +

0275.40 C                                and :#@OGAP in ('', OGAP00) +
0275.50 C                                 or :#@BKST in ('', bkst00) +
0275.60 C                                and :#@DSAP in ('', DSAP00) +
0275.70 C*                               and :#@MDSP in ('', MDSP00) +
0275.80 C*                               and :#@MDSR in ('', MDSR00) +
0275.90 C                                and :#@SHPA in ('', SHPA00) +
0276.00 C                                and :#@CONA in ('', CONA00) +
0276.10 C                                and :#@RDDT in ('', RDDT00) +
0276.20 C                                and :#@SVTY in ('', SVTY00) +
0276.30 C                                and :#@SDTM in ('', SDTM00) +
0276.40 C                                and :#@SDDT in ('', SDDT00) +
0276.50 C                                and :#@OSTA in ('', OSTA00) +
0276.60 C                                and :#@cst# in ('', cust00) +
0276.70 C                                and :#@cloc in ('', CLOC00) +
0276.80 C                                and :#@prod in ('', prod00) +
0276.90 C                                and :#@BLT# in ('', BLT#00) +
0277.00 C                                and :#@SLMN in ('', SLMN00) +
0277.10 C                                and :#SPODL in ('', SPODL0) '

When I step past that code and look in my log I see this:

   Variable #@HAST not defined or not usable.
3 >  *SYSTEM/DSPJOB
   Prepared statement NEWSTMT not found.
3 >  *SYSTEM/DSPJOB

#@HAST is a field from a display file that is used all through the pgm.  I
haven't done cursor in RPG for about 8 years so I bet it is something small.

Any input would be appreciated.  Thanks.





Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com




On Fri, Jul 29, 2011 at 2:34 PM, Schutte, Michael D <
Michael_Schutte@xxxxxxxxxxxx> wrote:

Sharon, I use this method a lot.

Where :selectedValue in (' ', fld1)
 And :selectedValue2 in (0, fld2)

Basically it's saying if selectedValue is blank then return all records
(your forcing the statement to always be true ' ' = ' ').  However, if
populated, then only return records where fld1 equals selected value. Then
the same thing with selected value2. If zero then return all records,
otherwise where fld2 matches selectedvalue2.  I firmly believe that it
performs better than using OR.

Try a sample statement in STRSQL.

Select * from unitmaster a
Where :region in (0, a.region)

Replace region any number and see the results.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 12:54 PM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm, take in another
direction

Hi Charles, can you give me more insight in this part of code you sent:

where :selectedValue in (' ', fld1);
 order by
    case when :selectedValue = ' ' then myfield else fld1;

Selected value would be on my field value and in the parens you have a
blank, then a fld1

If I have multiple fields to go against how would that work?  Would I build
the selected value in the way you don't suggest?  Haven't done SQL in RPG
for a while.

I could do the case statement in my where part of the SQL statement, that
would account for as many fields as I want I would think.

And also thanks for your input.




Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com




On Fri, Jul 29, 2011 at 12:35 PM, Charles Wilt <charles.wilt@xxxxxxxxx
wrote:

Dynamic SQL is a possible solution...but if you use it, make sure
you're doing it with parameters

wSqlStmt = 'select myfield from mytable where fld1 = ?';

exec SQL
 prepare S1 from wSqlStmt;
exec SQL
 open C1 using :selectedValue;

and not this:
wSqlStmt = 'select myfield from mytable where fld1 = ' + selectedValue;
exec SQL
 prepare S1 from wSqlStmt;
exec SQL
 open C1;

which is open to SQL injection.

Dynamic SQL is not usually required however; static SQL can handle
most variable WHERE and ORDER BYs and usually performs better...

exec SQL
 select myfield from mytbale
  where :selectedValue in (' ', fld1);
  order by
     case when :selectedValue = ' ' then myfield else fld1;




On Fri, Jul 29, 2011 at 11:33 AM, Monnier, Gary <Gary.Monnier@xxxxxxxxx>
wrote:
As others have said it sounds like Dynamic SQL is the way you want to
go.
--
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.

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any
attachments, may contain confidential information that is intended only for
the person or entity to which it is addressed. Any unauthorized review, use,
disclosure or distribution is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message and any attachments.
--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.