|
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 surelist
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
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 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.
midrange.com
