This is an old revision of the document!
Misc tips/reminders
- Concatenate with +
- free-form supports +=
- RTVQMQRY
- RTVQMFORM
- Check system files in QSYS2 lib
- SYSCST (constraints)
- SYSINDEXES
- SYSKEYS (key fields for indexes)
- SYSTABLES
- SYSTYPES (UDTs)
ILE
- compile modules with CrtRpgMod
- use CrtPgm to bind modules into a callable program
- can use CrtBndRPG to do both steps (creates a temp *Module object), but it may limit the ILE features
- UPDPGM can update (re-attach) a module to the main object
- CVTRPGSRC converts RPGIII code to RPGIV
ARRAYS
There are three types of arrays:
- The _run-time array_ is loaded by your program while it is running.
- The _compile-time array_ is loaded when your program is created. The initial data becomes a permanent part of your program.
- The _prerun-time array_ is loaded from an array file when your program begins running, before any input, calculation, or output operations are processed.
This will Z-ADD 15 to every element of ARRAY
C Z-ADD15 ARRAY
Each element of ARRAY1 will be added to the corresponding element of ARRAY2
C ADD ARRAY1 ARRAY2
BIFs
%Abs
- absolute value
%Char
%Check(look_for: string_to_search)
- first position in string_to_search that is not a value in look_for
%Date(optional_date: optional_date_format)
- if no date supplied, then get current date
%Days
- number of days as a duration
%Dec
- convert to packed number
%Dech
- convert to packed number (half-adjusted)
%DecPos
- # of decimal digits
%Diff
- diff between 2 dates/times
%Div
- divide 2 numbers
DIV = %DIV(A:B); (divide A by B)
%Editc(Number: 'X': *CURSYM)
- edit code (numeric)
- Common codes (the X, above, or used in O specs)
- 1-4 have no negative sign
- 5-9 are user-defined @ system level
- A-D use CR for negative values
- J is most common (commas, dec separator, - for neg, shows zeros)
- K (commas, dec separator, - for neg, zeros suppressed)
- Y - for dates
|Edit Code Description|No Sign|CR Sign|-Sign (R)|-Sign (L)|
| Commas and zero balances | 1 | A | J | N |
| Commas | 2 | B | K | O |
| Zero balances | 3 | C | L | P |
| No commas or zero balances | 4 | D | M | Q |
| User-defined edit codes | 5-9 |
| Date edit (4 digits) | W |
| Date edit | Y |
| Suppress leading zeros | Z |
%Editw
- edit word
- first zero ends zero-suppression (leading zeros after that position are printed)
- first asterisk also ends zero-suppression, but causes leading asterisks in the ouput
- 'b*bbbbbb.bb' (each b is a blank/space character) applied to 12345 prints *123.45 * a currency symbol followed by a zero floats * 'bb,bbb,b$0.bb' applied to 12 prints $.12 * a leading currency symbol always appears in that position * '$b,bbb,bb0.bb' applied to 123456 prints $bbb1,234.56 * an ampersand (&) causes a blank in the output * any other character prints that exact character * 'bb/bb/bb' aplied to 12345 prints b1/23/45 * '0bb/bb/bb' aplied to 12345 prints b01/23/45 %Elem : # of elements or occurrences %EOF(filename) : “1” if EOF %Equal : “1” if SETLL or LOOKUP found an exact match %Error : “1” if op code with “E” extender hit an error %Fields : update record %fields(salary:status); %Float : converts to float %Found(optional_filename) : “1” if found (CHAIN, DELETE, SETGT, SETLL), an element (LOOKUP), or a match (CHECK, CHECKR, SCAN) %Int %Inth : half adjusted integet %Len %Lookupxx(argument: array{:start index {:number of elements}} : array index of the matching element %Occur(DS) : active occurrence # of the DS %Open(filename) : “1” if open %Parms : # of parms passed into pgm %Rem : remainder<code rpgfree> REM = %REM(A:B);</code> %Replace(string: into_string: optional_start: optional_length) %Scan(look_for: look_in: optional_start) : 1st position of look_for within look_in %Size : size of a variable or text string (not length) <code rpgfree> DIM(%SIZE(OTHER_FIELD)) Use %size and %substr to extract 3 concatenated fields WITHOUT hard-coding the field sizes eval #cono = %subst($$id:1:%size(#cono)) eval #cusn = %subst($$id:%size(#cono)+1:%size(#cusn)) eval #dseq = %subst($$id:%size(#cono)+%size(#cusn)+1:%size(#dseq))</code> %Subdt(date: unit) : “date substring” - portion of a date <code rpgfree> %SUBDT(value:*MSECONDS|*SECONDS|*MINUTES|*HOURS|*DAYS|*MONTHS|*YEARS) %SUBDT(value:*MS|*S|*MN|*H|*D|*M|*Y)</code> %Subst : substring(string:start:length) <code rpgfree> After the EVAL the original value of A contains ’abghijklmno’ %SUBST(A(3:4))= ’’;</code> %Time %Timestamp %TLookupxx(argument: search table {: alternate table} : “1” if match, “0” otherwise %Trim %Triml %Trimr %Uns : unsigned number %XFoot(array) : sum of the elements %Xlate(Lower:Upper:String)
===== C-Specs ===== CAT(P) : optional (P) = pad with blanks (on right side) : CAT FLD1:2 FLD2 - put 2 blanks between FLD1 and FLD2 CHECK CHECKR CLEAR : Only initializes Output or Both fields - NOT Input Only fields Eval : “normal” eval - same as MOVEL Eval(R) : forces intermediate numbers to have the same precision as the result Evalr : eval with right adjust - same as MOVE command FEOD : Force End of Data (forces an EOF condition) : does not close files FOR : for i = %len(field) downto 1; : for i = 1 to %len(field); : for i = 1 by 2 to %len(field); (same as STEP 2 in VBA) GOTO : jump-to points are defined with TAG opcode Max() Min() SCAN SUBST TIME : if the result file is 6,0, then it returns just HHMMSS : if the result file is 12,0, then it returns hhmmssMMDDYY TRIM TRIML TRIMR XFOOT : Adds all elements of an array into a result field Continuation Lines for C-Specs : - each pair below has the same meaning : - in the last example, you could use (-) instead of (+), but then the next line must start in position 1 of the field<code rpgfree> C eval x = a + b C eval x = a + C b C eval x = 'abc' C eval x = 'ab+ C c'</code> Continuation for field names use elipses<code rpgfree>C eval MyLongF… C ieldName = A + B</code> That even works on D-Spec names, constants, and literals * note that the 'S'tandalone indicator and field size are on the next line<code rpgfree> D QuiteLongFieldNameThatCannotAlwaysFitInOneLine… D S 10A * DNUMERIC C 12345 D 67</code>
===== CALL ===== <code rpgfree>C CALL 'PGM' BBERLR C VAR1 PARM VAR2 PASS1 10</code> * the value of VAR2 is placed in PASS1, which is 10A, return value placed in VAR1 * BB = empty/blank value * ER = indicator seton if error in called pgm * LR = indicator seton if called pgm seton LR CALLB(D/E) : used to call bound procedures : (D) include operational descriptors : (E) enables exception handling CALLP(E M/R) : used to call prototyped procedures or programs
: (fmt) can be *DMY, *MDY, or *YMD
DATFMT(fmt{separator})
: ISO is the default
DEBUG{(*NO | *YES)}
: specifies whether DUMP operations are performed
DFTACTGRP(*YES | *NO)
: see ACTIVATION GROUPS, below
EXPROPTS(*MAXDIGITS | *RESDECPOS)
: same as M and R opcode extenders : controls precision of intermediate calcs
FORMSALIGN{(*NO | *YES)}
: if *YES, the first line of output files with 1P indicator can be printed repeatedly, allowing you to align the printer
GENLVL(number)
: for compile errors
NOMAIN
: for modules
OPTION(*{NO}XREF *{NO}SECLVL *{NO}SHOWCPY *{NO}EXPDDS *{NO}EXT *{NO}SHOWSKP) *{NO}SRCSTMT) *{NO}DEBUGIO)
: SECLVL = second-level message text : SHOWCPY expands /COPY directives : EXPDDS expands DDS listings of externally described files : EXT expands external procedure listings : SHOWSKP includes code skipped cuz of /IF directives : NOSRCSTMT indicates that line numbers from /COPY directives are assigned sequentially : DEBUGIO generates breakpoints for all input and output specifications
PRFDTA(*NOCOL | *COL) - for profiling (not performance) data TRUNCNBR(*YES | *NO) USRPRF(*USER | *OWNER)
: *OWNER = adopted authority
===== hex constants =====
* X'C17DC2'
===== ILE =====
* ILE lets you bind modules from different languages (RPG can be bound to CL) * first step is to compile modules with CrtRpgMod * use CrtPgm to bind modules into a callable program * can use CrtBndRPG to do both steps (creates a temp *Module object), but it may limit the ILE features * if using a NOMAIN subprocedure, you cannot use INZSR or *ENTRY PLIST * scope - all variables outside of subprocedures are global * scope - all variables within subprodedures are local
==== ACTIVATION GROUPS ====
* QILE is default activation group for modules * QSRVPGM is default activation group for service pgms * default ACTGRPs limit the ILE funcions * can't bind objects * pgm CALLs can only call other pgms, not procedures * allows partitioning of job resources (file overrides, committment definitions, and open files) * can control job activation and deactivation, improving performance * allows use of shared ODPs * if using mixed ILE and non-ILE, use default activation group * when an ILE pgm ends, the system doesn't always remove the storage for that pgm activation from the job * manual use of RCLACTGRP can improve performance
==== BINDING DIRECTORIES ====
* is just a list of modules * simplifies binding by storing multiple objects * H-Spec can contain the BndDir keyword * AddBndDirE adds new modules or service programs * WrkBndDir * RmvBndDirE
==== CALLS ====
* for normal (aka, dynamic) CALLs, system resolves all call references at runtime * CALLB for modules = Call Bound = binds by copy * CALLB uses traditional parm list * CALLB for service pgms = Bind by Reference (creates symbolic link to the procedure) * CALLP = Call with Prototype * Prototype must be defined in D-specs of both calling pgm and called procedure * passes parms by reference, by value, or read-only reference
==== NAMING CONVENTONS (suggested for ILE objects) ====
* MOD prefix for subprocedures/prototypes * SPG prefix for service programs * BND prefix for binding directories (BND0001) * source created as *module suffixed with "M" * CRTPGM source members suffixed with "R"
==== PROCEDURE INTERFACE (PI) ====
* Declares the entry parameters for the procedure * Parms must be in same order as the prototype * Parms *do not* have to use the same names as the prototype parms
==== PROTOTYPES (PR) ====
* A prototype is a definition of the call interface * A return value (if any) is specified on the PR definition
Keywords (valid for both the return value and parms)
* DATEFMT() * DIM() * LIKE() * LIKEDS() * LIKEREC() * PROCPTR - is a procedure pointer * TIMEFMT() * VARYING
Keywords valid for parms
* OPTIONS(*NOPASS) - parm is optional * VALUE - parm is passed by value
==== SERVICE PROGRAMS ====
* does not have a program entry point (it is never called directly) * is a collection of modules bound into a single object * only 1 copy ever exists * always bound by reference
==== SUBPROCEDURES ==== * can pass parms to subprocedures * can define local data and variables * can return a value * can be used in expressions * A subprocedure is a procedure specified after the main source section. * It can only be called using a bound call * EXPORT is required on “B”egin line of procedure if it wil be called by other modules * if no EXPORT, can only be used by current module ==== Code Sample ====
* Prototype for procedure FUNCTION D FUNCTION PR 10I 0 D TERM1 5I 0 VALUE D TERM2 5I 0 VALUE D TERM3 5I 0 VALUE P Function B *------------------------------------------------------------- * This procedure performs a function on the 3 numeric values * passed to it as value parameters. * * This illustrates how a procedure interface is specified for a * procedure and how values are returned from a procedure. *------------------------------------------------------------- D FUNCTION PI 10I 0 D TERM1 5I 0 VALUE D TERM2 5I 0 VALUE D TERM3 5I 0 VALUE D Result S 10I 0 /free Result = Term1 ** 2 * 17 + Term2 * 7 ?5% + Term3; return Result * 45 + 23; /end-free P E
Indicators * saving indicators
C MOVEA *IN,61 SAV8 8 C EXSR SUB1 C MOVEA SAV8 *IN,61
This can also be used for nested subroutines, IF you use different save field names for each subroutine
* Internal indicators (1P, LR, MR, and RT) are defined by the RPG IV program cycle itself * External indicators (U1-U9) can be turned on in CL programs, and can be used as file indicators * if indicator is off when pgm is called, the file is not opened * Halt indicators - H1-H9 * Overflow indicators - *INOA through *INOG, *INOV, and *IN01 through *IN99 * record identifying indicators - defined in the I specs * control level indicators - L1-L9 - only for primary or secondary files * field indicators - 01-99, H1-H9 - greater than zero, less than zero, zero, or blank * function key indicators - for F1-F24 - KA-KM (KO is not used) and KN-KY * field record relation indicators - 01-99, H1-H9, L1-L9, LR, U1-U8, RT - on I specs for internally-described files * resulting indicators - 01-99, H1-H9, OA-OG, OV, L1-L9, LR, U1-U8, KA-KN, KP-KY (valid only with SETOFF), RT * can test the result field after an arithmetic operation, identify a record-not-found condition, indicate an exception/error condition for a file operation, or to indicate an end-of-file condition. * Position 7 & 8 of C specs can have L1-L9 or LR
CL1 * a calc on this line would only run when L1 is onCLR * a calc on this line would only run when LR is on
===== Justify a Field ===== Left Justify
* Find the first non-blank character in FIELD. C ' ' CHECKFIELD X * BEFORE, FIELD= " MY TEXT " * AFTER, FIELD= "MY TEXT " * Extract into itself, starting at that position C X IFNE *ZERO C SUBSTFIELD:X FIELD P C ENDIF
Right Justify
* Find the LAST non-blank character in FIELD. C ' ' CHEKRFIELD X * BEFORE, FIELD= "MY TEXT " * AFTER, FIELD= " MY TEXT" C X IFNE *ZERO * Determine number of Trailing Blanks. C 30 SUB X X Concatenate FIELD into itself, which moves the trailing blanks to the front C ' ' CAT FIELD:X FIELD P C ENDIF
Center
* BEFORE, FIELD= "XXXXXXXXXX " * AFTER, FIELD= " XXXXXXXXXX " * Find the LAST non-blank character in FIELD. C ' ' CHEKRFIELD X X=10 * Subtract position of last non-blank character from the length of the output field, which gives the total number of blanks in the output field. C 50 SUB X X X=40 * Get the number of blanks that will be on each side of the data. C X DIV 2 X X=20 * Concatenate FIELD into CENTER, putting X number of blanks in front of the data C ' ' CAT FIELD:X CENTER P
===== Level Indicators ===== If L5 is turned on, then all lower levels (L1 - L4) are also turned on. The LR indicator automatically turns on all L1 - L9 indicators. Printing and calculations will occur from low to high (L1 > L9)
* can be manually attached to fields of an internally-described file
* L1 (below) is attached to the DEPT field
IQSYSPRT ... ... ... I 2 3 DEPT L1 I 4 15 SLMN
===== Literals =====
H DATFMT(*ISO) * Examples of literals used to initialize fields DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++ D DateField S D INZ(D'1988-09-03') D NumField S 5P 1 INZ(5.2) D CharField S 10A INZ('abcdefghij') D UCS2Field S 2C INZ(U'00610062') * Even though the date field is defined with a 2-digit year, the * initialization value must be defined with a 4-digit year, since * all literals must be specified in date format specified * on the control specification. D YmdDate S D INZ(D'2001-01-13') D DATFMT(*YMD) * Examples of literals used to define named constants D DateConst C CONST(D'1988-09-03') D NumConst C CONST(5.2) D CharConst C CONST('abcdefghij') * Note that the CONST keyword is not required. D Upper C 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' * Note that the literal may be continued on the next line D Lower C 'abcdefghijklmn- D opqrstuvwxyz'
==== float literals ==== The following lists some examples of valid float literals:
* 1E1 = 10 * 1.2e-1 = .12 * -1234.9E0 = -1234.9 * 12e12 = 12000000000000 * +67,89E+0003 = 67890 (the comma is the decimal point)
===== LOKUP =====
C VALUES LOKUP LOOK_INTO HiLoEq
Lookup for Related Tables
C VALUES LOKUP LOOK_INTO RELATED HiLoEq
If a match is found, the “field” called related contains the matching value
===== Messages ===== Sending Messages Directly from the RPG Program
C MSG,1 DSPLYUSERMQ* Factor 2 (MsgQ) can be blank * Factor 1 contains the message to display. * Factor 2: if blank, and the job is batch, the message is sent to QSYSOPR * if blank, and the job is interactive, sent to *EXT msgq
C *MUSR0001 DSPLYUSERMQ* Sends message USR0001 from MSGF QUSERMSG. You can use OVRMSGF to use a different msg file * If the result field is a variable, the program waits for a response, & puts the response in the variable!
===== MISC. ===== /EJECT
/SPACE #
SCR01
SFL01
CTL01
CMD01
LDA - to make updates to it available to called pgms, must do OUT first
Optional way to define an array as a field:E FLD 12 20 I$FLD DS I 1 240 FLD
PGMA calls PGMB, which uses RETRN - when PGMA ends w/ LR on, it doesn't close PGMB's files! You could call PGMB w/ a parm, which tells it to end w/ LR on, or call QCMDEXC from PGMA, executing RCLRSC Record formats - reading more than 1 from the same screen
C WRITE ABOVE C WRITE BELOW C EXFMT SFLCTL C READ ABOVE 90 C READ BELOW 91
Record Locks - use an error indicator for CHAIN or READ to know if you have a record lock READE w/ a key - it locks the next record, then compares the key values - this means the first record after the key value will get locked by this process - it also means that this “wrong” record could create a wait state External PRTFs (instead of O-Specs) allow you to use the same pgm to create 2 or more different (but similar) reports - create the 2nd PRTF (using some of the original fields, or extra fields, different column or report headings, etc.) using LVLCHK(*NO) - use OVRPRTF to direct the output to the new PRTF - you just created a new report w/o doing any RPG coding! KLIST - you can define fields sizes on KFLD lines Using CAT, you can “MOVEL” up to 16 characters in one operation:
C '12345678'CAT '90ABCDEF' RESULT
===== Monitor (for errors) =====
c monitor * start monitor c eval ivinue = ivinue + movc95 * eval equation c on-error 0103 * field too small c eval ivinue = *hival * reset c endmon * end monitor
===== O-Specs ===== * A “B” near the column # means “blank after” * An “F” means Fetch * Before printing the total line, the pgm “fetches” all heading output controled by “OF”
===== QMQRY ===== * Use & for field names
<code sql>WHERE CONO55 = &CONO</code> * Use SETVAR in the CL <code sql>STRQMQRY QMQRY(*LIBL/DONOFRTQ) SETVAR((CONO &CONOW) (LOCD &LOCDW))</code>
===== Record Format ===== * describes the fields: names, order, size/length, data type, column heading, validity checks, etc.
===== Renaming fields in RPG ===== You can easily rename INPUT fields in RPG using the I specs, but this doesn't work for Output-only files. To accomplish this, define the file as Input, Full Procedural w/ Add, and add a “dummy” read - now you can re-define your “output” field.
===== RRN ===== * The RRN of a record, even from a keyed file, is in positions 397-400 of INFDS (in Binary). * The INFDS is updated only when a block of records is written
* may need to use %%OVRDBF SEQONLY(*YES 1)%%
===== SCAN ===== 4 is placed in RESULT, and *IN66 is turned on
HiLoEq C 'ABC' SCAN 'XYZABCII' RESULT 5566
The Scan starts at position 3
C 'ABC' SCAN FLDA:3 RESULT 5566
===== SDS =====
I SDS I 1 10 $$PGM I 244 253 $$WS I 254 263 $$USER I 264 269 $$JOB
===== Shared Print File ===== Multiple programs can write to the same printer file - start w/ OVRPRTF FILE(QSYSPRT) SHARE(*YES) The 1st RPG program can be a simple little thing that only opens the file:
FQSYSPRT O F 132 OF PRINTER C RETRN C EXCPTDUMMY C MOVE *ON *INLR OQSYSPRT E DUMMY
You can end the process by using RCLRSC in the CL. As an alternative, you can pass a parm to the first & last programs to control opening & closing the file.
===== SQL =====
DECLARE ... OPEN ... FETCH NEXT ... Loop, CHECK FOR Error Code CLOSE...
alias
: an alternate name for a table, view, or member <code sql>CREATE ALIAS MYLIB.MYMBR2_ALIAS FOR MYLIB.MYFILE(MBR2)</code>
Alter Table
: add/drop/alter columns : add/drop constraints
Comment On
: adds long text (2000 char max for V5R3) to object
Constraint
: database rules (unique, referential, or check)
Create commands create 2 names - an SQL name (128 char max) and a system name (10 char max) Create Table (PF)
%%Create Table xxx As Select...%% : creates a table using a subset of field from an existing table %%Create Table xxx Like MyTable...%% : all fields in referenced table used in created table
Create View
: (LF - unkeyed)
Create Index
: (LF - keyed)
data dictionary
: a set of tables containing object definitions
DECLARE cname CURSOR
: the default "declare cursor" only allows 1 retrieval per record, and there's no navigating backwards
DECLARE cname SCROLL CURSOR
: to enable fetch prior or fetch first, use a scrollable cursor
Drop
: deletes database objects
Fetch Next From … For 10 Rows Into :DS Functions (UDFs)
: execute like BIFs (meaning, inline within code)
Index
: keyed LF
Insert Into MyFile 10 Rows Values :DS Label On
: adds text (50 chars) and/or column heading (60 chars)
Rename
: for objects, including system name portions of object names : Rename Table Customer_Master To SYSTEM NAME CustMast
schema
: consists of a library, a journal, a journal receiver, a catalog, and optionally, a data dictionary
sequence
: a data area object that provides a quick and easy way of generating unique numbers
Stored Procedure
: executed via CALL statement
System Name
: Create commands create 2 names - an SQL name (128 char max) and a system name (10 char max) : see below for SQL over system table : may want to rename them
trigger
: a set of actions that are run automatically whenever a specified event occurs to a specified base table
UDT
<code sql>CREATE DISTINCT TYPE US_DOLLAR AS DECIMAL (9,2)</code> : can then create tables with field types of US_DOLLAR (or string, integer, etc.)
view
: can point to multiple tables, can have a subset of columns
———— ==== Create Function (UDF) from a different source member ====
CREATE FUNCTION CUSPRC (IN VARCHAR(8), VARCHAR(3), VARCHAR(15)) RETURNS VARCHAR(30) EXTERNAL NAME '&LIB/GETCUSPRCF(CUSPRC)' LANGUAGE RPGLE PARAMETER STYLE SQL NOT DETERMINISTIC
==== Create Function (UDF) from included code ====
CREATE FUNCTION PMGTPOOQ (CONO VARCHAR(2), ORDN VARCHAR(7) , ITEM VARCHAR(15)) RETURNS DECIMAL(15,3) LANGUAGE SQL NOT DETERMINISTIC BEGIN DECLARE ORDQTY DECIMAL(15,3); SELECT SUM(OQTY03) INTO ORDQTY FROM PMP03 WHERE CONO03=CONO AND ORDN03=ORDN AND ITEM03=ITEM AND DTLC03 = 9999999 AND BOSN03=0 AND ACRC03=' ' AND DLTF03=' ' AND SUBSTR(ORDN03,1,1) IN('P','S'); RETURN ORDQTY; END
==== Cursor sample ====
C/EXEC SQL C+ declare cGLC1 cursor for C+ select max(prdt) C+ into :w_pdate C+ from C+ where C/END-EXEC c/exec sql c+ open cGLC1 c/end-exec c dow all_cglc1 = *off c/exec sql c+ fetch next from cGLC1 c+ into :#pstamt, :#sesno, :#prlssn c+ c/end-exec c if sqlcod = 0 * process data c else * EOF (or an error) c/exec sql c+ close cGLC1 c/end-exec c endif c enddo
==== “insert into” example ====
C/EXEC SQL C+ insert into cusprcp C+ C+ Select C+ 'C', C+ CONO70 CONO, C+ LOCD70 LOCD, C+ CUSN70 CUSN, C+ DSEQ70 DSEQ, C+ CATN70 CATN, C+ :pdate PRDT C+ from C+ inp20 C+ Join oep70 on cono20 = cono70 and strc20 = locd70 C+ C+ Where C+ CONO20 = 'D1' C+ and DEPC20 = 'MFG' C+ and pern70 > LEFT(DEC(DEC(Replace(CHAR(CURRENT DATE - 13 Months, C+ ISO), '-', '')) - 19000000,7,0) ,5) C+ and lqty70 > 0 C+ GROUP BY C+ CONO70, C+ LOCD70, C+ CUSN70, C+ DSEQ70, C+ CATN70 C+ C/END-EXEC
==== Select Into a DS ====
D Pmp02Rec E DS EXTNAME(Pmp02) EXEC SQL SELECT * INTO :Pmp02Rec FROM PMP02 WHERE ORDN02=SUBSTRING(:Lda,349,7) AND DTLC02=9999999;
==== Simple SQL without a cursor ====
EXEC SQL SELECT DSCUSCO||DSCUSN||DSDSEQ INTO :LocationValue FROM OEIDSPF WHERE DSPO=:Po; IF SqlCod<>0; LocationValue=*BLANK; ENDIF;
==== System Name from SYSTABLES ==== * Is there a SYSTABLES for each schema? * Also in QSYS2
SELECT System_Table_Name, System_Table_Schema, TABLE_NAME FROM SysTables WHERE TABLE_NAME = "Customer_Master"
==== “set option” example ====
C/exec sql C+ set option commit=*none,closqlcsr=*endmod C/end-exec C/EXEC SQL C+ select max(prdt) C+ into :w_pdate C+ from cusprcp C+ where prtyp = 'C' C/END-EXEC C/EXEC SQL C+ delete from cusprcp C+ where prtyp = 'C' C/END-EXEC C/EXEC SQL C+ update cusprcp C+ set prtyp = 'H' C+ where prtyp = 'C' C/END-EXEC
===== Stored Procedures ===== V7 can process the result set from a proc RPG stored procedures have always been able to return a result set * But we could not receive/process that result set in an RPG program This new support may feel a bit “clunky” - But it works - You need to … * Define a RESULT_SET_LOCATOR (defined on D spec) * Then ASSOCIATE the Result Set Locator with the Procedure * Then ALLOCATE the CURSOR for the result set
D CustResultSet S SQLType(RESULT_SET_LOCATOR) Exec SQL Call CustomersByState( :InputState ); Exec SQL Associate Result Set Locator (:CustResultSet) with Procedure CustomersByState; Exec SQL Allocate C1 Cursor for Result Set :CustResultSet; Exec SQL Fetch next from C1 into :CustData;
===== Strings ===== ^ Traditional Syntax ^ Free-Form Syntax ^ | CAT | + operator | | CHECK | %CHECK | | CHECKR | %CHECKR | | | %STR | | | %REPLACE | | SCAN | %SCAN | | SUBST | %SUBST | | XLATE | %XLATE | | TRIM | %TRIM | | TRIML | %TRIML | | TRIMR | %TRIMR |
===== Strip Leading Zeros ===== Find the first non-zero non-blank character - Note that Factor 1 contains a space, followed by a Zero
C ' 0' CHECK FIELD X
* BEFORE, FIELD= “000048006” * If any non-zero non-blank character was found. AFTER, FIELD= “ 48006”
C X IFNE *ZERO
Translate all zeros to blanks
C '0':' ' XLATE FIELD FIELD
Translate all blanks back to zeros, starting at the position of the first non-zero non-blank character
C ' ':'0' XLATE FIELD:X FIELD C ENDIF
===== Subfiles ===== CSRLOC(ROW COLUMN)
: where to make the cursor appear
Error Handling
: turn on indicator for DSPATR(RI PC) for fields with errors
Page Size
: if page size = SFL size, then pgm must handle PAGEUP and PAGEDN : also, if the last record exactly fills the page, the pgm shows More, even though there are no more : read ahead to see if we're @ EOF, then readp, then show More or Bottom
ROLLDOWN (same as PAGEUP) - use same indicator as SFLDSP ROLLUP (same as PAGEDOWN) - use same indicator as SFLDSP RTNCSRLOC(&CSRRCD &CSRFLD &CSRFLP)
: 2 formats - this format retrieves record name, field name, and option al cursor location within the field
RTNCSRLOC(*WINDOW &CSRROW &CSRCOL &WDWROW &WDWCOL)
: 2 formats - this format retrieves the cursor row and column
SFLCLR
: clear the subfile - duh! : Use the same indicator as SFLDSPCTL, except SFLDSP uses "Not on"
SFLDSP
: Only show/display the SFL when this is on
SFLDSPCTL
: Only show/display the control record when this is on
SFLNXTCHG
: forces record to appear changed : user presses Enter, validate screen, errors found, turn on SFLNXTCHG and error indicator, write back to screen : cuz SFLNXTCHG is on, if the user just presses Enter, the error records will appear changed
===== Subprocedures ===== * see ILE section
===== TEST ===== The test operations are: TEST
: Test Date/Time/Timestamp
TESTB
: Test Bit
TESTN
: Test Numeric
TESTZ
: Test Zone
===== XLATE ===== Translate characters in Factor 2 based on Factor 1 * Factor 1: FROM:TO * Factor 2: Field containing data to Translate. Optional colon, followed by starting position * Result Field: Translated result is placed here. Translate all lower case characters to upper case (LWR & UPR need to be constants or tables)
C LWR:UPR XLATE FIELD FIELDOld-School Column Markers
*...1....+....2....+....3....+....4....+....5....+....6....+....7... IFilename++SqNORiPos1+NCCPos2+NCCPos3+NCC................................ I........................Fmt+SPFrom+To+++DcField+++++++++L1M1FrPlMnZr.... DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++ CL0N01Factor1+++++++Opcode(E)+Factor2+++++++Result++++++++Len++D+HiLoEq.. OFilename++DF..N01N02N03Excnam++++B++A++Sb+Sa+........................... O..............N01N02N03Field+++++++++YB.End++PConstant/editword/DTformat
- blog/iseries/rpg.1583583628.txt.gz
- Last modified: 2020/03/07 04:20
- by
tom
Discussion