find()
Here’s how to use the find() function.
A.f ind()
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:
T.find(k,…)
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.
|
T.find( k;x:C,.. )
Description:
Find a record from an entity table/multizone composite table according to the specified primary key value.
Syntax:
T.find(k;x:C,..)
Note:
The function gets the record whose primary key value is k from an entity table or a multizone composite table and returns a record consisting of field C or all fields if parameter C is absent. The key value k can be represented by the first dimension value which can uniquely identify the record.
When T has the time key, the function finds the record whose time key value is not greater than the largest of k values among those that share same basic key value. Use now() to get the current time if no time key is written in parameter k.
Parameter:
|
T |
An entity table or a multizone composite table |
|
k |
The key value; write multiple key values as a sequence; time key value is allowed |
|
x |
Column name; retrieve all field if omitted |
|
C |
Column alias; can be omitted |
Option:
|
@k |
Find multiple records according to multiple primary key values and return them as a record sequence; by default the function returns the first-found record whose primary key is k ; to search for multiple records according to a composite primary key, write parameter k in the format of [[k1,k2],[...],...] |
Return value:
A record/record sequence
Example:
When T is the base table:
|
|
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.
|
When T is an attached table:
|
|
A |
|
|
1 |
=file("ctb.ctx").open() |
Open a composite file. |
|
2 |
=A1.attach(table2) |
Return attached table table2 in the composite table. |
|
3 |
=A2.find(1,2) |
Get records whose primary key values are 1 and 2 respectively from A2’s attached table.
|
|
4 |
=A2.find@k([[1,2],[3,6]]) |
Use @k option to find multiple records.
|
When entity table/multizone composite table T has the time key:
|
|
A |
|
|
1 |
=demo.cursor("select top 10 STATE,HIREDATE,EID,NAME from EMPLOYEE ").sortx(STATE,HIREDATE) |
|
|
2 |
=file("ef.ctx") |
|
|
3 |
=A2.create@yt(#STATE,#HIREDATE,EID,NAME) |
Create a composite table, where STATE is the basic key and HIREDATE is the time key. |
|
4 |
=A3.append@i(A1) |
Append cursor A1’s data to the composite table’s base table, and below is the latter’s content:
|
|
5 |
=A4.find(["Texas",date("2006-03-12")]) |
Find the record that has the largest time key value among records whose time key values are not greater than 2006-09-12 within records where STATE is “Texas”.
|
|
6 |
=A4.find("Texas") |
Use now() to calculate the time key value, which is the latest date.
|
When entity table/multizone composite table T is a multizone composite table:
|
|
A |
|
|
1 |
=connect("demo").cursor("select EID,NAME,GENDER from employee") |
Return a cursor. |
|
2 |
=file("ef.ctx":[1,2]) |
Define a homo-name files group: 1.ef.ctx and 2.ef.ctx. |
|
3 |
=A2.create@y(#EID,NAME,GENDER;if(GENDER=="F",1,2)) |
Create a multizone composite table, set EID as its key, and put records where GENDER is F to 1.ef.ctx and the other records to 2.ef.ctx. |
|
4 |
=A3.append@ix(A1) |
Append cursor A1’s data to A3’s multizone composite table. |
|
5 |
=A4.find(3) |
Get the record whose primary key value is 3 from A4’s multizone composite table, retrieve all fields as parameter x is absent, and return the following record:
|
|
6 |
=A4.find(3;NAME) |
Get the record whose primary key value is 3 from A4’s multizone composite table, retrieve NAME field only, and return the following record:
|
|
7 |
=A4.find@k([3,6]) |
Use @k option to search for multiple records and result is as follows:
|
