BSCAN

Traverses the unique keys in an index, or the item ids in an inode-type file.

Synopsis

BSCAN keyvar [,recvar] 
[FROM filevar[,startkey]] 
[USING indexname] [RESET] [BY seq] 
[THEN statements] [ELSE statements]

Arguments

keyvar	BSCAN assigns to keyvar the key or item id returned by the BSCAN operation.
recvar	Optional — If you specify a recvar, BSCAN assigns the contents associated with keyvar to it. This can be the list of item ids associated with the key returned in keyvar, or the contents of the record associated with the item id returned in keyvar.
FROM filevar	Optional — A local variable name assigned to the MultiValue file by the OPEN statement. If you do not specify filevar, the default file, specified in the system variable @STDFIL, is used.
startkey	Optional — An expression that specifies the relative starting position of the scan. startkey can be an index key or item id. If the USING indexname clause is used, startkey is a value in the specified index.
USING indexname	Optional — The name of a secondary index associated with the file.
BY seq	Optional — Specifies the direction of the scan. The available seq options are “A” (ascending) and “D” (descending). The default is ascending.

Description

The BSCAN statement operates in 2 modes:

BSCAN with an indexname steps through the unique keys in an index. The keys are returned as keyvar. It optionally returns the item id associated with keyvar as recvar.
BSCAN without an indexname steps through the item ids in an inode-type file. The list of item ids is returned as keyvar. It optionally returns the contents of each keyvar item as recvar.

The BSCAN statement scans the leaf nodes of either a B-tree file (type 25) or a secondary index. The record ID returned by the scan operation is assigned to keyvar. If you specify a recvar, BSCAN assigns the contents of the keyvar record to it.

filevar specifies an open file. If you do not specify filevar, the default file is used. (For more information on default files, see the OPEN statement.) If the specified file is neither accessible nor open, BSCAN returns nothing and sets STATUS() to 3.

startkey is an expression that evaluates to a record ID of a record in the B-tree file. If the USING clause is used, startkey is a value in the specified index. startkey specifies the relative starting position of the scan.

startkey need not exactly match an existing record ID or index key. If it does not, the scan finds the next or previous record ID or value, depending on whether the scan is in ascending or descending order. For example, depending on how precisely you want to specify the starting point at or near the record ID or value SMITH, startkey can evaluate to SMITH, SMIT, SMI, SM, or S.

If you do not specify startkey, on the initial BSCAN operation, the scan starts at the beginning (leftmost slot of the leftmost leaf) or end (rightmost slot of the rightmost leaf) of the index or file, depending on the value of the seq expression. The scan then moves in the direction specified in the BY clause. Subsequent BSCAN operations with no startkey specified will continue from the keyvar returned by the previous BSCAN.

indexname is an expression that evaluates to the name of a secondary index associated with the file.

RESET resets the internal scan pointer to the first or last key, depending on the BY seq clause value. If you do not specify seq, the scan is done in ascending order. If you specify startkey in the FROM clause, RESET is ignored.

seq is an expression that evaluates to A or D; it specifies the direction of the scan. "A", the default, specifies ascending order. "D" specifies descending order.

You can optionally specify a THEN clause, an ELSE clause, or both a THEN and an ELSE clause. If the BSCAN statement finds a valid index key, or item id and its associated data, the THEN clause is executed. If the scan does not find a valid index key, or if some other error occurs, the ELSE clause is executed. The statements argument can be the NULL keyword, a single statement, or a block of statements terminated by the END keyword. A block of statements has specific line break requirements: each statement must be on its own line and cannot follow a THEN, ELSE, or END keyword on that line.

Any file updates executed in a transaction (that is, between a BEGIN TRANSACTION statement and a COMMIT statement) are not accessible to the BSCAN statement until after the COMMIT statement has been executed.

Note:

Cache supports the BSCAN statement for compatibility with legacy MultiValue systems. When retrieving keys from an index, developers should use OPENINDEX and SELECT with the ATKEY clause, because SELECT ATKEY has a simpler, more intuitive syntax and superior performance.

STATUS Values

The STATUS function returns the following values after the BSCAN statement is executed:

If NLS is enabled, the BSCAN statement retrieves record IDs in the order determined by the active collation locale; otherwise, BSCAN uses the default order, which is simple byte ordering that uses the standard binary value for characters; the Collate convention as specified in the NLS.LC.COLLATE file for the current locale is ignored.

0	The scan proceeded beyond the first or last key. keyvar and recvar are set to empty strings.
1	The scan returned an existing index key, or an index key that matches the key specified by startkey.
2	The scan returned an index key that does not match startkey. keyvar is either the next or the previous record ID in the B-tree, depending on the direction of the scan.
3	filevar is not open, or is not an inode-type.
4	indexname does not exist.
5	seq does not evaluate to A or D.
6	The index specified by indexname needs to be built.
10	An internal error was detected.

Examples

The following example demonstrates using BSCAN to step through the keys in an index:

0001 EXECUTE 'CREATE-FILE DATE-FILE'
0002 OPEN 'DICT','DATE-FILE' TO DICT.DATE.FILE ELSE STOP 201,'DICT DATE-FILE'
0003 WRITE 'D':@AM:1 ON DICT.DATE.FILE,'DAY.OF.WEEK'
0004 EXECUTE 'CREATE-INDEX DATE-FILE DAY.OF.WEEK'
0005 OPEN 'DATE-FILE' TO DATE.FILE ELSE STOP 201,'DATE-FILE'
0006 FOR I=1 TO 21
0007   ID=DATE()+I
0008   WRITE OCONV(ID,'DWA') ON DATE.FILE,ID
0009 NEXT I
0010 LOOP
0011   BSCAN DOW,IDLIST FROM DATE.FILE USING 'DAY.OF.WEEK' ELSE EXIT
0012   CRT 'DOW=':DOW,' DATES=':IDLIST
0013 REPEAT

This returns the following output:

[421] DICT for file 'DATE-FILE' created. Type = INODE
[418] Default data section for file 'DATE-FILE' created. Type = INODE
[437] Added default record '@ID' to 'DICT DATE-FILE'.
[417] CreateFile Completed.
DOW=FRIDAY       DATES=14642 14649 14656
DOW=MONDAY       DATES=14645 14652 14659
DOW=SATURDAY     DATES=14643 14650 14657
DOW=SUNDAY       DATES=14644 14651 14658
DOW=THURSDAY     DATES=14641 14648 14655
DOW=TUESDAY      DATES=14646 14653 14660
DOW=WEDNESDAY    DATES=14640 14647 14654

Notice that on each iteration, BSCAN returns the next unique key in the index. The item ids associated with the key are returned as an @AM-delimited list in the optional recvar argument. If you want to process each record for the key, you need to code a loop to do so.

The following example demonstrates using BSCAN to retrieve the keys for a particular key:

0001 OPEN 'DATE-FILE' TO DATE.FILE ELSE STOP 201,'DATE-FILE'
0002 BSCAN DOW FROM DATE.FILE,'SUN' USING 'DAY.OF.WEEK' ELSE NULL
0003 CRT 'DOW=':DOW,' STATUS=':STATUS()
0004 BSCAN DOW FROM DATE.FILE USING 'DAY.OF.WEEK' ELSE NULL
0005 CRT 'DOW=':DOW,' STATUS=':STATUS()
0006 BSCAN DOW FROM DATE.FILE,'Z' USING 'DAY.OF.WEEK' ELSE NULL
0007 CRT 'DOW=':DOW,' STATUS=':STATUS()