7.47.2 FETCH Examples
Example 1: Fetch fields #NAME, #ADDL1 and #POSTCD from the record in file CUSTMST that has key #CUSNUM:
FETCH FIELDS(#NAME #ADDL1 #POSTCD) FROM_FILE(CUSMST) WITH_KEY(#CUSNUM)
or identically:
GROUP_BY NAME(#CUSTOMER) FIELDS(#NAME #ADDL1 #POSTCD)
FETCH FIELDS(#CUSTOMER) FROM_FILE(CUSMST) WITH_KEY(#CUSNUM)
Example 2: Fetch a tax rate (#TAXRATE) from a table of valid tax codes. The first key to the table indicates the tax type which in this case is always "income tax" and the second is the tax code (#TAXCDE):
FETCH FIELDS(#TAXRATE) FROM_FILE(TAXTAB) WITH_KEY('INCOME' #TAXCDE)
Example 3: Fetch the product number (#PRODUCT) of the first order in an order line files where the #QUANTITY field is greater than 10:
FETCH FIELDS(#PRODUCT) FROM_FILE(ORDLIN) WHERE('#QUANTITY > 10')
Example 4: Read and print details of all general ledger transactions from a file called GLTRANS. Associated with each transaction is a company number (#COMPNO). Fetch the actual company name (#COMPNAME) from file COMPANY and include this on the report:
GROUP_BY NAME(#REPORTLIN) FIELDS(#TRANSNUM #TRANSTYP #TRANSVAL #COMPNO #COMPNAME)
SELECT FIELDS(#REPORTLIN) FROM_FILE(GLTRANS)
FETCH FIELDS(#COMPNAME) FROM_FILE(COMPANY) WITH_KEY(#COMPNO) KEEP_LAST(15)
UPRINT FIELDS(#REPORTLIN)
ENDSELECT
ENDPRINT
Note that if there were 10,000 transactions in GLTRANS, and 15 (or less) different companies, this program would perform at most 10,015 database I/O operations. If the KEEP_LAST parameter on the FETCH command was omitted it would perform 20,000 I/O operations, which would probably double its execution time.
Example 5: Fetch all fields from the currently active version of file CUSMST with key #CUSNUM:
FETCH FIELDS(*ALL) FROM_FILE(CUSMST) WITH_KEY(#CUSNUM)
Example 6: Fetch all real fields from the currently active version of file CUSMST but exclude address information:
GROUP_BY NAME(#XG_ADDR) FIELDS(#ADDL1 #ADDL2 #ADDL3 #POSTCD)
FETCH FIELDS(*ALL_REAL *EXCLUDING #XG_ADDR) FROM_FILE(CUSMST) WITH_KEY(#CUSNUM)