|
Very cool that this topic just came up. I'm real new to SQL of any type, in fact you helped me not long ago Tracy. Yesterday I stumbled upon the 'prepare' SQL and figured I'd give it a shot. The term spaghetti code comes to mind when looking at what I'm doing, but I didn't know how to attack this any other way but to build the entire statement. I have a selection screen where I can enter 0-20 store#'s, a date range, 0-5 divisions and 0-5 seasons. I'm building the SQL statement the way I used to fumble around with OPNQRYF. You'll see in the attached! Mike -----Original Message----- From: Tracy Ball [mailto:TBall@xxxxxxxxxxxxxxxx] Sent: Tuesday, February 25, 2003 12:48 PM To: RPG programming on the AS400 / iSeries Subject: RE: SQLRPGLE - Dynamic selection Does the nulls field have any bearing on the syntax here at all? This is a great example. From what I can see, I am doing pretty much all these things in just a much simpler text, yet don't get the results. I will use this as a model. Thank you very much!! -Tracy Ext. 3107 -----Original Message----- From: MWalter@xxxxxxxxxxxxxxx [mailto:MWalter@xxxxxxxxxxxxxxx] Sent: Tuesday, February 25, 2003 12:39 PM To: RPG programming on the AS400 / iSeries Subject: Re: SQLRPGLE - Dynamic selection something like this: DgetItemData PR 40 D itnbr 15 CONST D year 4 0 CONST PgetItemData B Export DgetItemData PI 40 D itnbr 15 CONST D year 4 0 CONST ditemDS ds D itdsc 30 overlay(itemDs:*next) D itcls 4 overlay(itemds:*next) D itacim 3 overlay(itemDs:*next) D unmsr 2 overlay(itemDs:*next) D valuc 1 overlay(itemDs:*Next) Dnulls S 5i 0 DsqlStmt S 250 DfileName S 21 c reset itemDs c eval fileName = 'Yearend/Itemasa' + c %subst(%editc(year:'X'):3:2) c eval sqlStmt = 'SELECT itdsc, itcls, itacim, + c unmsr, valuc + C FROM ' + %trim(fileName) + C ' WHERE itnbr = ''' + C %trim(itnbr) + '''' C/exec sql C+ PREPARE stmt from :sqlStmt c/end-exec c/exec sql c+ DECLARE c CURSOR FOR stmt c/end-exec c/exec sql c+ OPEN c c/end-exec c/exec sql C+ FETCH c INTO :itemDS :nulls c/end-exec c/exec sql c+ CLOSE c c/end-exec c return itemDs P E Thanks, Mark Mark Walter Sr. Programmer/Analyst Hanover Wire Cloth a div of CCX, Inc. mwalter@xxxxxxxxxxxxxxx http://www.hanoverwire.com 717.637.3795 Ext.3040 /"\ \ / X / \ _______________________________________________ This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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.
midrange.com
*****************************************************************
compile with option:
Delay PREPARE . . . . . . . . . DLYPRP *yes
*****************************************************************
Here's the sql statement before it's executed:
> EVAL sql_statement
SQL_STATEMENT =
....5...10...15...20...25...30...35...40...45...50...55...60
1 'select * from ediinvl5 where insol in ( 'H449 ', 'H440 ', 'H'
61 '436 ' ) and indiv in ( '3T', '1E', '1C' ) and insea in ( 'H''
121 ' ) '
*****************************************************************
* get edi inventory data
d #getInventory pr
* get edi sales data
d #getSales pr
* for sql statements
d wrkediinv e ds extname( EDIINVL5 )
d wrkedisls e ds extname( EDISLSL5 )
d alist ds
d pstr 100
d pwedt1 8 0
d pwedt2 8 0
d pDivisions 10a
d pSeasons 5a
d alist2 ds
d store# 1 100 dim( 20 ) inz( *hival )
d descend
d division 117 126 dim( 5 ) inz( *hival )
d descend
d season 127 131 dim( 5 ) inz( *hival )
d descend
d apos c const( X'7D' )
d comma c const( ',' )
d i s 5i 0
d nbrDivisions s 5i 0
d nbrSeasons s 5i 0
d nbrStores s 5i 0
d sql_statement s 1024a varying
d begin s d
d d s 7s 0
d end s d
d hlddiv s 2a
d hlddpt s 4a
d hldsol s 4a
d invend s 8s 0
d kendt s 6s 0
d kendt@ s 2s 0
d myDate s d
d nbrTimes s 7s 0
d runtim s 6s 0
d user s 10a
d wkdpt s 4a
d wkpf1 s 10a
d wkpf2 s 10a
d wkpf3 s 10a
d wkpf4 s 10a
d wkpf5 s 10a
**********************************************************************
* mainline logic *
**********************************************************************
* check sales
c callp #getSales
* check inventory
c callp #getInventory
/eject
**********************************************************************
* Procedure: #getInventory *
* Useage: *
**********************************************************************
p #getInventory b
d #getInventory pi
/free
// start to build the sql statement
sql_statement = 'select * from ediinvl5';
// if store numbers are passed build selection statement
nbrStores = %lookup( *blanks : store# ) - 1;
if nbrStores > 0;
sql_statement = sql_statement + ' where insol in (';
for i = 1 to nbrStores;
if i > 1;
sql_statement = sql_statement + comma;
endif;
sql_statement = sql_statement + ' ' + apos + store#(i) + apos;
endfor;
sql_statement = sql_statement + ' )';
endif;
// week ending date range
if nbrStores = 0;
sql_statement = sql_statement + ' where inend between '
+ %editc( pwedt1 : 'X' ) + ' and '
+ %editc( pwedt2 : 'X' );
else;
sql_statement = sql_statement + ' and inend between '
+ %editc( pwedt1 : 'X' ) + ' and '
+ %editc( pwedt2 : 'X' );
endif;
// if divisions are passed add to selection statement
nbrDivisions = %lookup( *blanks : division ) - 1;
if nbrDivisions > 0;
sql_statement = sql_statement + ' and indiv in (';
for i = 1 to nbrDivisions;
if i > 1;
sql_statement = sql_statement + comma;
endif;
sql_statement = sql_statement + ' ' + apos + division(i) + apos;
endfor;
sql_statement = sql_statement + ' )';
endif;
// if seasons are passed add to selection statement
nbrSeasons = %lookup( *blanks : season ) - 1;
if nbrSeasons > 0;
sql_statement = sql_statement + ' and insea in (';
for i = 1 to nbrSeasons;
if i > 1;
sql_statement = sql_statement + comma;
endif;
sql_statement = sql_statement + ' ' + apos + season(i) + apos;
endfor;
sql_statement = sql_statement + ' )';
endif;
/end-free
* prepare sql statement
c/exec sql
c+ prepare sql from :sql_statement
c/end-exec
* declare cursor
c/exec sql
c+ declare c1 cursor for sql
c/end-exec
* open cursor
c/exec sql
c+ open c1
c/end-exec
* fetch cursor
c/exec sql
c+ fetch c1 into :wrkediinv
c/end-exec
/free
dow ( sqlcod >= 0 ) and ( sqlcod <> 100 );
chain deptKey2 CHKDPTWF;
if %found( CHKDPTWF );
cdwinv = 'X';
cdwict = cdwict + 1;
update DPTREC;
else;
cdwsol = insol;
cdwdiv = indiv;
cdwdpt = indpt;
cdwsls = *blanks;
cdword = *blanks;
cdwinv = 'X';
cdwsct = 0;
cdwict = 1;
write DPTREC;
endif;
/end-free
* fetch cursor
c/exec sql
c+ fetch next from c1 into :wrkediinv
c/end-exec
/free
enddo;
/end-free
* close cursor
c/exec sql
c+ close c1
c/end-exec
p #getInventory e
/eject
**********************************************************************
* Procedure: #getSales *
* Useage: *
**********************************************************************
p #getSales b
d #getSales pi
/free
// start to build the sql statement
sql_statement = 'select * from edislsl5';
// if store numbers are passed build selection statement
nbrStores = %lookup( *blanks : store# ) - 1;
if nbrStores > 0;
sql_statement = sql_statement + ' where wksol in (';
for i = 1 to nbrStores;
if i > 1;
sql_statement = sql_statement + comma;
endif;
sql_statement = sql_statement + ' ' + apos + store#(i) + apos;
endfor;
sql_statement = sql_statement + ' )';
endif;
// week ending date range
if nbrStores = 0;
sql_statement = sql_statement + ' where wkend between '
+ %subst( %editc( pwedt1 : 'X' ) : 3 : 6 ) + ' and '
+ %subst( %editc( pwedt2 : 'X' ) : 3 : 6 );
else;
sql_statement = sql_statement + ' and wkend between '
+ %subst( %editc( pwedt1 : 'X' ) : 3 : 6 ) + ' and '
+ %subst( %editc( pwedt2 : 'X' ) : 3 : 6 );
endif;
// if divisions are passed add to selection statement
nbrDivisions = %lookup( *blanks : division ) - 1;
if nbrDivisions > 0;
sql_statement = sql_statement + ' and wkdiv in (';
for i = 1 to nbrDivisions;
if i > 1;
sql_statement = sql_statement + comma;
endif;
sql_statement = sql_statement + ' ' + apos + division(i) + apos;
endfor;
sql_statement = sql_statement + ' )';
endif;
// if seasons are passed add to selection statement
nbrSeasons = %lookup( *blanks : season ) - 1;
if nbrSeasons > 0;
sql_statement = sql_statement + ' and wksea in (';
for i = 1 to nbrSeasons;
if i > 1;
sql_statement = sql_statement + comma;
endif;
sql_statement = sql_statement + ' ' + apos + season(i) + apos;
endfor;
sql_statement = sql_statement + ' )';
endif;
/end-free
* prepare sql statement
c/exec sql
c+ prepare sql2 from :sql_statement
c/end-exec
* declare cursor
c/exec sql
c+ declare c2 cursor for sql2
c/end-exec
* open cursor
c/exec sql
c+ open c2
c/end-exec
* fetch cursor
c/exec sql
c+ fetch c2 into :wrkedisls
c/end-exec
/free
dow ( sqlcod >= 0 ) and ( sqlcod <> 100 );
wkdpt = %subst( alphf1 : 1 : 4 );
chain deptKey1 CHKDPTWF;
if %found( CHKDPTWF );
cdwsls = 'X';
cdwsct = cdwsct + 1;
update DPTREC;
else;
cdwsol = wksol;
cdwdiv = wkdiv;
cdwdpt = wkdpt;
cdwsls = 'X';
cdwinv = *blanks;
cdword = *blanks;
cdwsct = 1;
cdwict = 0;
write DPTREC;
endif;
/end-free
* fetch cursor
c/exec sql
c+ fetch next from c2 into :wrkedisls
c/end-exec
/free
enddo;
/end-free
* close cursor
c/exec sql
c+ close c2
c/end-exec
p #getSales e
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.
