Here’s how to use the find() function.
Description:
Find a record according to its primary key value.
Syntax:
A.find(k)
Note:
The function finds records from a record sequence/table sequence whose primary key values are k. Use the index table if there is one. When parameter A has a time key, among records that have same basic key values and where the time key value is not greater than the specified k value, find the record whose time key value is the largest. If there isn’t time key value in the primary key, take the time calculated through now() function, which is the latest time.
Parameter:
A |
A record sequence/table sequence |
k |
Primary key, which will be written as a sequence if it is a multi-field primary key. |
Option:
@b |
Enable the binary search. A must be ordered by the primary key; otherwise, the result will be wrong. The index table will be ignored. |
@k |
Enable to find multiple records; return members of A matching the sequence of key values specified by parameter k; by default, the function returns the first-found record whose primary key is k |
Return value:
Records
Example:
|
A |
|
1 |
=demo.query("select * from EMPLOYEE") |
|
2 |
=A1.keys(NAME,DEPT) |
|
3 |
=A1.find(["Alexis","Sales"]) |
A sequence is used since the key contains two fields |
4 |
=demo.query("select * from EMPLOYEE") |
This table sequence is ordered by the EID field |
5 |
=A4.keys(EID) |
|
6 |
=A4.find@b(3) |
@b is used to enable the binary search in order to speed up the computation |
7 |
=A4.find@k(2,15,69,220) |
With @k option, the function returns members matching the specified key values |
8 |
=A4.find(2,15,69,220) |
Return the record whose primary key value is matched as @k option is absent |
9 |
=demo.query("select EID,NAME,STATE,HIREDATE from EMPLOYEE where GENDER='M'") |
|
10 |
=A9.keys@t(STATE,HIREDATE) |
Set STATE as the basic key and HIREDATE as the time key |
11 |
=A9.find(["Florida",date("2006-03-12")]) |
Among records where STATE is “Florida” and HIREDATE is earlier than 2006-03-12, find the one whose time key value is the latest |
12 |
=A9.find("Florida") |
Calculate the time key value using now(), which is the latest date |
Related function:
Description:
Find the record(s) according to the specified primary key value(s) from an in-memory table.
Syntax:
T.find(k,…)
Note:
The function finds records where the primary key value is k from memory table T.Use binary search if there isn’t an index.
Parameter:
T |
An in-memory table |
k |
The primary key; write multiple keys as a sequence |
Option:
@k |
Find multiple records according to multiple primary keys and return them as a record sequence; by default the function returns the first-found record whose primary key is k ; write multiple primary keys in the format of [[k1,k2],[...],...] |
Return value:
A record/record sequence
Example:
|
A |
|
1 |
=demo.cursor("select EID,NAME,GENDER from employee where EID< 10") |
Return a cursor |
2 |
=A1.memory() |
Return an in-memory table |
3 |
=A2.keys(EID,NAME) |
Set EID and NAME as the keys of the in-memory table |
4 |
=A2.find([8,"Megan"]) |
The two primary keys are written as a sequence |
5 |
=A2.keys(EID,GENDER) |
Set EID and GENDER as the keys of the in-memory table |
6 |
=A2.find@k([[8,"F"],[2,"F"]]) |
Use @k option to find multiple records |
7 |
=A2.keys(EID) |
Set EID as the key of the in-memory table |
8 |
=A2.find(3) |
Find the record where EID is 3 |
Description:
Find the record(s) according to the specified primary key value(s) from a base table.
Syntax:
T.find(k;x:C,..)
Note:
The function finds the record(s) where key value(s) is k and returns a record/record sequence containing one field C. The key value k can be represented by the first dimension value which can uniquely identify a record.
Parameter:
T |
Base table |
k |
The primary key; write multiple keys as a sequence |
x |
Column name; retrieve all field if omitted |
C |
Column alias; can be omitted |
Option:
@k |
Find multiple records according to multiple primay keys and return them as a record sequence; by default the function returns the first-found record whose primary key is k ; write a composite primary key in the format of [[k1,k2],[...],...] |
Return value:
A record/record sequence
Example:
|
A |
|
1 |
=file("E:/find1.ctx") |
Return a cursor |
2 |
=A1.open() |
Open a composite table’s base table whose dimensions are EID and NAME |
3 |
=A2.find([8,"Megan"]) |
Since parameter x is absent, the function returns all columns |
4 |
=A2.find([8,"Megan"];EID,SALARY) |
Return specified columns |
5 |
=A2.find@k([[4,"Emily"], [8,"Megan"]];EID,SALARY) |
Find multiple records with @k option |