Data Set Data Model: _setKey

Data Set Data Model: _setKey

Sets an index key for retrieving the rows in the table

Syntax
Details
Example

Syntax

CALL SEND (object-id, '_setKey', rc<, keyname<, condition<, scroll<, val-list>>>>);

Argument Type Description

rc
N returns 0 if the key was successfully applied, nonzero otherwise

keyname
C specifies the name of the key or index to use on the table. The keyname may specify a single or compound index.

condition
C specifies the condition to use when comparing the key value:

equal to

greater than or equal to

greater than

less than or equal to

less than

scroll
C specifies whether rows can be retrieved in random order:

'SCROLL' rows can be retrieved in random order (default)

'NOSCROLL' rows can only be retrieved sequentially

val-list
N specifies the identifier of an SCL list that contains values to use in the key. The item name should reflect the appropriate column name, and the item value should be the value that the key value is compared against.

Argument	Type	Description
rc	N	returns `0` if the key was successfully applied, nonzero otherwise
keyname	C	specifies the name of the key or index to use on the table. The keyname may specify a single or compound index.
condition	C	specifies the condition to use when comparing the key value:
		equal to
		greater than or equal to
		greater than
		less than or equal to
		less than
scroll	C	specifies whether rows can be retrieved in random order:
		'SCROLL'	rows can be retrieved in random order (default)
		'NOSCROLL'	rows can only be retrieved sequentially
val-list	N	specifies the identifier of an SCL list that contains values to use in the key. The item name should reflect the appropriate column name, and the item value should be the value that the key value is compared against.

Details

The _setKey method enables you to set an active key in an open table to a simple or composite key. It establishes a set of criteria for reading table rows by evaluating the value of the columns against the key value in the rows.

Using a composite key with _setKey operates the same way as the _where method only when the condition is EQ. The value returned when the condition is EQ is the same as if the columns specified in the composite key were connected by WHERE conditions that use AND or ALSO.

For all other conditions (GT, GE, LT, or LE) specified with _setKey for a composite key, the composite key columns are concatenated together to form the index key. The number returned by the _keyCount method is the number of rows in the table that satisfy the composite key. For example, if the composite index consists of columns SEX and AGE and the condition is GT (greater than), the values to search for are concatenated such that key values of F for SEX and 13 for AGE yield an index key of F13. Because the search is performed on the concatenated values, some values may meet the search condition that you did not expect, such as SEX of M and AGE of 11, because the string M11 is considered greater than the string F13.

Once an active key is set through the _setKey method, it remains active until the following conditions are met:

the table is closed
another key is set
the current setting is cleared by passing the rc argument alone to the _setKey method.

The table is automatically positioned at the row that meets the specified criteria.

The _setKey method cannot be used in conjunction with a WHERE clause.

The _setKey method sets SYSRC for error, note, and warning conditions.

Example

The following example creates an index on the STATE column in the table SASUSER.CRIME. It subsets on STATE values less than 20. In this example, COUNT returns 15. This example assumes you have created a frame with a data table named TABLE.

INIT: dsid=open('sasuser.crime', 'v'); rc=icreate(dsid, 'state', 'state'); dsid=close(dsid);

call notify('.', '_getWidget', 'TABLE', tabid); call send(tabid, '_setDataset', 'sasuser.crime');

list=makelist(); list=setnitemn(list, 20, 'state'); call send(tabid, '_setKey', rc, 'state', 'LT', 'scroll', list); call send(tabid, '_keyCount', rc, count); put count=; return; TERM: list=dellist(list); return;

Chapter Contents
Previous
Next
Top of Page