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) from left to right beginning from the first member until the first ineligible member 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:
A sequence or a 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:7000) |
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) |
Search backwards and return [4,4,5] |
|
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 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 alreaded 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:
Select records from a pseudo table according to a specified condition.
Syntax:
|
T.select(x) |
|
Note:
The function calculates expression x on each record of pseudo table T and returns a pseudo table containing records that make x true. It returns a pseudo table retaining all its 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:
A 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) |
Get records that satisfy eid>7 from A2’s pseudo table and return it
|
|
4 |
=A3.import() |
Get a table sequence from pseudo table object A3
|
|
5 |
=A2.select() |
Return A2’s pseudo table having all its original records |
|
6 |
=A5.import() |
Get a table sequence from pseudo table object A5
|
T.select( x;f )
Description:
Write pseudo table records that cannot meet the specified condition into a fin file.
Syntax:
T.select(x;f)
Note:
The function computes expression x on each record of pseudo table T, writes records that make x return false into fin file f, and returns a pseudo table containing records that make x return true.
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) |
Write pseudo table records that do not meet the condition DEPT=="HR" into bin file emp_NOTHR.btx in the main directory |
|
5 |
=A4.import() |
Below is content of the returned pseudo table:
|
ch. select()
Description:
Return a channel with records that meet the given condition.
Syntax:
ch.select(x)
Note:
The function calculates expression x against each of the records in channel ch, and returns the channel containing records that make values of x true. When the parameter x is omitted, return the original channel with all records. This is an attached computation.
Parameter:
|
ch |
Channel |
|
x |
Boolean expression |
Return value:
The original channel with records that meet the given condition
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SALES") |
|
|
2 |
=channel() |
Create a channel |
|
3 |
=A2.select(ORDERID>100) |
Attach a ch.select() operation to A2’s channel to get records meeting the condition ORDERID>100 and return the channel with these eligible records |
|
4 |
=A2.fetch() |
Fetch and store the existing data in the channel |
|
5 |
=A1.push(A2) |
Be ready to push data in A1’s cursor into A2’s channel |
|
6 |
=A1.fetch() |
Fetch data from A1’s cursor, and it is this time that data in the cursor is truly pushed to the channel and computations start |
|
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 ch’
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:
Get records meeting the given condition from a cursor.
Syntax:
cs.select(x)
Note:
The function calculates expression x against each of the records in cursor cs and returns a cursor containing records that can make the value of x true. When parameter x is omitted, return the original cursor with all records. The function supports multicursors.
Parameter:
|
cs |
A cursor |
|
x |
A boolean value |
Return value:
The original cursor with eligible records
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SCORES") |
|
|
2 |
=A1.select(STUDENTID>10) |
Select the records where STUDENTID is greater than 10 from the SCORE table |
|
3 |
=A2.fetch() |
|
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 |
Cursor |
|
x |
A boolean value |
|
ch’ |
Channel |
Return value:
Channel ch’
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:
Write records that do not satisfy the specified expression into a bin file.
Syntax:
cs.select(x;f)
Note:
The function computes expression x over each record of cursor cs, writes the records that do not satisfy expression x into the bin file f, and returns the original cursor containing records that make expression x’s result true.
Parameter:
|
cs |
A cursor |
|
x |
A Boolean expression |
|
f |
A bin file |
Return value:
The original cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from dept") |
|
|
2 |
=file("dept.btx") |
|
|
3 |
=A1.select(DEPTID<5;A2) |
Write records that cannot meet condition DEPTID<5 into bin file dept.btx in the main directory |
|
4 |
=A3.fetch() |
|
