Directives
SELECT Select/Query From ... Where
   
Format
1. Open, read and query records:
SELECT iolist [,REC=string$ ] FROM { [ TABLE ] filename$ | chan }[ ,KNO=num | name$ ]
  [ BEGIN key$ [ : key$ : ... ] ]
  [ END key$ [ : key$ : ... ] ]
  [ WHERE expression ]
  [ ,ERR=stmtref ]
   
2. Return full record contents:
SELECT RECORD iolist FROM { [ TABLE ] filename$ | chan }[ ,KNO=num | name$ ]
  [ BEGIN key$ [ : key$ : ... ] ]
  [ END key [ : key$ : ... ] ]
  [ WHERE expression ]
  [ ,ERR=stmtref ]
   
3. Return key portion of the record:
SELECT KEY iolist [,REC=string$ ] FROM { [ TABLE ] filename$ | chan }[ ,KNO=num | name$ ]
  [ BEGIN key$ [ : key$ : ... ] ]
  [ END key$ [ : key$ : ... ] ]
  [ WHERE expression ]
  [ ,ERR=stmtref ]

Where:

chan Channel or logical file number to be read from.
expression Condition must return true or false. Numeric or string expression.
filename$ Name of the file to be opened and read from. String expression.
iolist List of variables to be read from the file. The order in which the variables are specified (A$, B$, C$, ..) corresponds to how the fields are read from each record (1st, 2nd, 3rd, ..). If you use an asterisk * then all fields defined by the embedded data dictionary will be returned. You can use an IOL=iolref as your iolist.
KNO=num | name$ File access key value (num) or name (name$).
BEGIN key$ [ : key$ : ... ] Starting key of the range to select. key$ is a string expression. Multiple colon separated segments may be specified if needed.
END key$ [ : key$ : ... ] Ending key of the range to select. key$ is a string expression. Multiple colon separated segments may be specified if needed.
string$ Record prefix (REC=VIS(string$) can also be used).
stmtref Program line number or statement label to which to transfer control.
   
Description Use the SELECT directive to open, read and query records from the specified data file or just to read data from a specified file number. As each record is read, ProvideX processes any logic you include following the SELECT directive up to the NEXT RECORD. When ProvideX encounters a NEXT RECORD statement with no records found for a nested SELECT, it will locate the corresponding SELECT statement.

If you include a WHERE clause, ProvideX will process only those records WHERE the condition is true.

The BEGIN and END clauses are only supported for Keyed and Memory files. You can use these clauses with WindX-connected files. Note that, if you are using BEGIN and END in SELECT statements for files with descending keys, the END value must be lower than the BEGIN value.

*Note* Every SELECT must have a corresponding NEXT RECORD directive, and must be in the correct sequence. A mis-matched number of SELECT and NEXT RECORD directives can result in either an Error #27: Unexpected or incorrect WEND, RETURN, or NEXT, or an Error #28: No corresponding FOR for NEXT.
  Because ProvideX pads descending keys to their full length with $FF$, the BEGIN value is item$+$FF$ and the END value should be item$+$00$ so that the ending key is less than the beginning key; i.e., the correct format is currently SELECT * FROM filename BEGIN item$ END item$+$00$.

Although the incorrect statement SELECT * FROM filename BEGIN item$ END item$+$FF$may have worked in prior versions, it no longer does as of Version 4.20. You must either include a NEXT RECORD directive to end the SELECT loop or instruct ProvideX to exit the loop early (with an EXITTO directive).When an EXITTO directive is used, the file will be closed if SELECT specified a data filename rather than a channel.

Also, in earlier versions of ProvideX, the CONTINUE and BREAK directives (and corresponding *CONTINUE and *BREAK labels) were not supported for use with SELECT / NEXT RECORD directives. As of Version 4.20, it is currently possible to BREAK and CONTINUE commands in SELECT structures.

USING the TABLE option +PxPlus Exclusive

The keyword TABLE before the name$ indicates that the value provided is the logical table name for the file as defined in the currently opened Data Dictionary file. See OPEN DICTIONARY for more details.
   
Formats 2 and 3 SELECT RECORD and SELECT KEY Options

The SELECT .. NEXT RECORD statement can include syntax for full record contents or key portion of the record. SELECT RECORD allows the specification of a single variable, or * as per the READ RECORD directive. SELECT KEY reads the file and treats the key contents as if it were the data. A single variable can be specified to receive the key value or a formatted IOList; e.g.,

SELECT KEY DIV:[CHR(2)], CLIENT:[CHR(7)] FROM "ABC".

Refer to the examples below.
   
Read next Physical If you specify KNO=* on the SELECT directive, the system will read and return the next physical record from the file. The read will NOT utilize any of the key tables in order to access the data. This significantly reduces the overhead involved in the data retrieval process.

Use of the Read Next Physical capability can greatly reduce the time it takes to process a file where the order of the data is not important.

The read next physical capability is a +PxPlus Exclusive
   
See Also NEXT RECORD End SELECT Statment
BREAK Immediate Exit of Loop,
CONTINUE Initiates Next Iteration of Loop
EXITTO End Loop, Transfer Control.
Structured Error handling using ON ERR - an +PxPlus Exclusive
   
Example The following illustrates use of the SELECT WHERE clause:

0010 SELECT IOL=0100 FROM "VEND_FILE",KNO=1 BEGIN "ABC CO." END "THAT CO." WHERE
0010:CITY$="CLARENDON"
0020 PRINT REC(IOL=0100)
0030 NEXT RECORD
0100 IOLIST VEND$,NAME$,ADDR1$,ADDR2$,CITY$,PROV$,POSTAL$,INV_DT$,INV,
0100:AMT,TERMS,DUE_DT$
0110 PRINT "DONE"; END

In the following example, SELECT KEY is issued to return the key portion of the record:

SELECT KEY cst_name$,REC=X1$ FROM "cstfile",KNO=1 BEGIN StartName$ END StartName$+$FF$ WHERE x1.cst_name$<>SkipName$
PRINT x1.cst_name$
NEXT RECORD

This uses the SELECT RECORD directive to return the full record contents:

SELECT RECORD r$ FROM "cstfile",KNO=1 BEGIN "D" END "D"+$FF$ PRINT r$
NEXT RECORD