select()
Here’s how to use select() functions.
A. select()
Description:
Return members of a sequence which satisfy a condition.
Syntax:
|
A.select(x) |
|
|
A.select(x1:y1, x2:y2, ......xi:yi) |
The simplified syntax of "&&" for a multiple-condition query, which is equal to A.select(x1== y1 && x2== y2 &&...... xi==yi) |
Note:
The function computes expression x against each member of the sequence A, and returns a new sequence composed of those members which make x return true. When there are no parameters, it returns all the members. Note that if the name of a to-be-referenced column of the sequence is the same as a cell name, the sequence name should be attached before the column name in the expression.
Parameter:
|
A |
A sequence. |
|
x |
A Boolean expression, which can be null. |
|
xi:yi |
xi is an expression, and yi is a comparing value. |
Option:
|
@1 |
Return the first member that fulfills the condition. |
|
@z |
Search the members from back to front. |
|
@b |
Use binary search, which requires that A is an ascendingly ordered sequence and that parameters are separated by colon or they are expressions returning numeric values; the eligible members are found when result of expressions are 0. |
|
@m |
Use parallel algorithm to handle data-intensive or computation-intensive tasks; no definite order for the records in the result set; it can’t be used with @1bz options. |
|
@t |
Return an empty table sequence with data structure if the grouping and aggregate operation over the sequence returns null. |
|
@c |
Enable getting the eligible member(s) beginning from the first member that makes Boolean expression x true from left to right until the first ineligible member that makes Boolean expression x false appears. |
|
@r |
Enable searching for the first eligible members from left to right and getting all members after it (inclusive) until the last one. |
|
@v |
Return result as a pure table sequence when sequence A is a pure table sequence; return a pure sequence when this option is absent. |
Return value:
Sequence/Table sequence
Example:
|
|
A |
|
|
1 |
[2,5,4,3,2,1,4,1,3] |
|
|
2 |
=A1.select(~>3) |
[5,4,4]. |
|
3 |
=A1.select@1(~>3) |
5; return the first eligible member. |
|
4 |
=demo.query("select EID,NAME,GENDER,DEPT,SALARY from EMPLOYEE order by EID") |
|
|
5 |
=A4.select(GENDER:"F",SALARY:16000) |
Multi-condition query.
|
.
Specify search direction:
|
|
A |
|
|
1 |
[2,5,4,3,2,1,4,1,3] |
|
|
2 |
=A1.select(~>3) |
[5,4,4]. |
|
3 |
=A1.select@z(~>3) |
[4,4,5]; search backwards. |
|
4 |
[8,10,3,5,7,9,11,13,7] |
|
|
5 |
=A4.select@c(~>7) |
Return [8,10]; search from the first member until the first ineligible member appears. |
|
6 |
=A4.select@zc(~>6) |
Return [7,13,11,9,7]; search backwards until the first ineligible member appears. |
|
7 |
=A4.select@r(~>10) |
Return [11,13,7]; Search for the first eligible member from the first member in order and get all members after it (inclusive). |
High-efficiency search and filtering:
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,GENDER,DEPT,BIRTHDAY from employee") |
|
|
2 |
=A1.select@m(GENDER=="F") |
@m option enables parallel computation when there is a large volume of data to increase performance. |
|
3 |
=A1.sort(EID) |
Sort A1 by EID in the ascending order. |
|
4 |
=A3.select@b(EID<10) |
As A3 is an ascending sequence, here we use binary search to perform the query, for which parameter x should be a numeric expression. |
When a column name and a cell name are same:
|
|
A |
|
|
1 |
=to(3).new(~:ID,~*~:A1) |
|
|
2 |
=A1.select(A1.A1==4) |
As the column name and the cell name are same, the column name should be suffixed by sequence name when it is referenced in an expression.
|
Return a pure table sequence:
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,GENDER,DEPT,BIRTHDAY from employee").keys(EID) |
|
|
2 |
=A1.i() |
Convert table sequence A1 to a pure table sequence. |
|
3 |
=A2.select(GENDER=="M") |
Return a pure sequence. |
|
4 |
=A2.select@v(GENDER=="F") |
@v option enables returning a pure table sequence. |
Related function:
Description:
Return members of a table sequence which satisfy a condition.
Syntax:
T.select@i(x)
Note:
The function computes expression x against each member of table sequence T, and returns a new table sequence composed of those members which make x return true
T is a table sequence for which an index is already created. T.select@i() will reuse the table sequence’s index and order of records in the result set will probably be disrupted.
The function returns all members when parameter x is absent.
Parameter:
|
T |
A table sequence for which an index is already created |
|
x |
Filtering expression; can be null |
Return value:
A table sequence
Example:
|
|
A |
|
|
1 |
=demo.query("select * from DEPT").keys@i(DEPTID) |
Return a table sequence whose key is DEPTID and create the hash index for it.
|
|
2 |
=A1.select@i(FATHER==12) |
Reuse the index created in A1 and return the record where FATHER is 12 in A1’s table sequence.
|
T.select()
Description:
Define a record filtering operation on a pseudo table and return a new pseudo table.
Syntax:
T.select(x)
Note:
The function defines a computation on pseudo table T, which will calculate expression x on each of its records and get records that make x true, and return a new pseudo table.
It returns a pseudo table retaining all records when parameter x is absent.
Parameter:
|
T |
A pseudo table |
|
x |
A Boolean expression that is a filter condition, which can be null |
Return value:
Pseudo table object
Example:
|
|
A |
|
|
1 |
=create(file).record(["D:/file/pseudo/app.ctx"]) |
Below is data in composite table app.ctx:
|
|
2 |
=pseudo(A1)
|
Generate a pseudo table object. |
|
3 |
=A2.select(eid>7) |
Define a computation on A2’s pseudo table, which will get records that satisfy eid>7, and return a new pseudo table. |
|
4 |
=A3.import() |
Import data from A3’s pseudo table while executing the computation defined in A3 on A2’s pseudo table, and return the following table:
|
|
5 |
=A2.select() |
Define a computation on A2’s pseudo table, which will get all records, and return a new pseudo table. |
|
6 |
=A5.import() |
Import data from A5’s pseudo table while executing the computation defined in A5 on A2’s pseudo table, and return the following table:
|
T.select(x;f)
Define a record filtering operation on a pseudo table, return a new pseudo table, and write ineligible records to a bin file.
Syntax:
T.select(x;f)
Note:
The function defines a computation on pseudo table T, which will compute expression x on each of its records, get records that make x return true and return them to a new pseudo table, and writes records that make x return false into bin file f.
Parameter:
|
T |
A pseudo table |
|
x |
A Boolean expression |
|
f |
A bin file |
Return value:
Pseudo table
Example:
|
|
A |
|
|
1 |
=create(file).record(["EMPLOYEE.ctx"]) |
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from a composite table. |
|
3 |
=file("emp_NOTHR.btx") |
|
|
4 |
=A2.select(DEPT=="HR";A3) |
Define a computation on A2’s pseudo table, which will get records that meet the condition DEPT=="HR" and return them to a new pseudo table, and write records not meeting the condition into bin file emp_NOTHR.btx in the main directory. |
|
5 |
=A4.import() |
Import data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and generate bin file emp_NOTHR.btx. Below is the data A5 returns:
|
ch. select()
Description:
Attach a record filtering computation to a channel and return the orignal channel.
Syntax:
ch.select(x)
Note:
The function attaches a computation to channel ch, which will calculate expression x on each of its records and get records that make values of x true, and returns the original channel ch.
When parameter x is absent, get all records.
This is an attachment function.
Parameter:
|
ch |
A channel |
|
x |
Boolean expression |
Return value:
Channel
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SALES") |
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=A2.select(ORDERID>100) |
Attach a computation to A2’s channel, which will get records meeting the condition ORDERID>100, and return the original channel. |
|
4 |
=A2.fetch() |
Fetch and keep the current data in the channel. |
|
5 |
=A1.push(A2) |
Be ready to push data in A1’s cursor to A2’s channel. |
|
6 |
=A1.fetch() |
Fetch data from cursor A1 while pushing data to channel A2 to execute the attached computation and keep the result. |
|
7 |
=A2.result() |
Get result from the channel.
|
ch.select(x,ch’ )
Description:
Send records in a channel that can’t meet the given condition into another channel.
Syntax:
ch.select(x,ch’)
Note:
The function calculates expression x against each of the records in channel ch, and sends those records over which the value of x is false into channel ch’.
Parameter:
|
ch |
Channel |
|
x |
A Boolean expression |
|
ch’ |
Channel |
Return value:
Channel
Example:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,SALARY from EMPLOYEE " ) |
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=channel() |
Create a channel. |
|
4 |
=A1.push(A2) |
Push data in A1’s cursor into A2’s channel. |
|
5 |
=A2.select(EID<5,A3) |
Push records in A2’s channel that can’t meet the condition EID<5 into A3’s channel. |
|
6 |
=A2.fetch() |
Attach ch.fetch() function that gets the final result set to A2’s channel to fetch and store the existing data in A2’s channel. |
|
7 |
=A3.fetch() |
Attach ch.fetch() function that gets the final result set to A3’s channel to fetch and store the existing data in A3’s channel. |
|
8 |
=A1.skip() |
It is when fetching data from A1’s cursor begins that data in the cursor is truly pushed to the channel and computations start. |
|
9 |
=A2.result() |
Get result of the computation in channel A2. |
|
10 |
=A3.result() |
Get result of the computation in channel A3. |
cs .select()
Description:
Attach the record filtering action to a cursor and return the original cursor.
Syntax:
cs.select(x)
Note:
The function attaches a computation to cursor cs, which will calculate expression x against each of the records in cursor cs and get records that make value of x true, and return the original cursor cs.
When parameter x is omitted, get all records of cursor cs. The function supports multicursors.
This is a delayed function.
Option:
|
@c |
Enable getting the eligible member(s) beginning from the first member that makes Boolean expression x true from left to right until the first ineligible member that makes Boolean expression x false appears. |
|
@v |
Use column-wise computation when parameter cs is a column-wise cursor and also return a column-wise cursor. |
Parameter:
|
cs |
A cursor |
|
x |
A Boolean value |
Return value:
Cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SCORES") |
Return a cursor:
|
|
2 |
=A1.select(STUDENTID>10) |
Attach a computation to cursor A1, which will select records where STUDENTID is greater than 10 from the cursor, and return cursor A1:
|
|
3 |
=A1.fetch() |
Fetch data from cursor A1 where A2’s computation is executed (it would be better that data is fetched in batches when a large amount of data is involved):
|
When using @v option:
|
|
A |
|
|
1 |
=connect("demo").cursor@v("select * from employee") |
Return a column-wise cursor. |
|
2 |
=A1.select@v(DEPT=="HR") |
Perform column-wise computation. |
When using @c option:
|
|
A |
|
|
1 |
=[4,8,10,3,5,7,9,11,13,7].cursor() |
|
|
2 |
=A1.select@c(~>7) |
@c option enables to get members from the first member greater than 7 until the first member less than 7 appears. |
|
3 |
=A2.fetch() |
Result: [8,10]. |
cs.select(x,ch’ )
Description:
Send records in a cursor that can’t meet the given condition into a channel.
Syntax:
cs.select(x,ch’)
Note:
The function calculates expression x against each of the records in cursor cs, and sends those records over which the value of x is false into channel ch’.
Parameter:
|
cs |
A cursor |
|
x |
A Boolean value |
|
ch’ |
A channel |
Return value:
Channel
Example:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,SALARY from EMPLOYEE" ) |
|
|
2 |
=channel() |
Create a channel. |
|
3 |
=A1.select(EID<5,A2) |
Push cursor A1’s records not meeting condition EID<5 into A2’s channel. |
|
4 |
=A2.fetch() |
Fetch and store the existing data in the channel. |
|
5 |
=A1.fetch() |
Fetch data from the filtered cursor.
|
|
6 |
=A2.result() |
Get result from the channel.
|
cs.select(x;f)
Description:
Attach the record filtering action to a cursor and return the original cursor while writing records that not satisfy the specified expression to a bin file.
Syntax:
cs.select(x;f)
Note:
The function attaches a record filtering action to cursor cs, which will compute expression x over each record of cursor cs, get records that make value of x true, return the original cursor cs and write the records that do not satisfy expression x into bin file f.
This is a delayed function.
Parameter:
|
cs |
A cursor |
|
x |
A Boolean expression |
|
f |
A bin file |
Return value:
Cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from dept") |
Return a cursor:
|
|
2 |
=file("dept.btx") |
Generate a bin file object. |
|
3 |
=A1.select(DEPTID<5;A2) |
Attach a computation to cursor A1, which will get records that meet DEPTID <5, return cursor A1 and write records that cannot meet the condition to bin file dept.btx:
|
|
4 |
=A1.fetch() |
dept.btx will be really generated after data is really fetched out from cursor A1; below is data in cursor A1 where the computation is executed (it would be better that data is fetched in batches when data amount is large):
|
