Here’s how to use cursor() function.
Description:
Call a cellset file and return the result set returned from the execution of the file as a cursor.
Syntax:
cursor(dfx,…)
Note:
The function calls a cellset file and returns the result set returned from the execution of the file as a cursor. When there are multiple return statements in the dfx file, first merge the result sets returned by the return statements, and then return the merge result as a cursor. These result sets of return must be of the same structure, or error will be reported. Use either an absolute path or a relative path to search for the dfx file. With the relative path, the search order is Class path –> Search path –> Main directory. The cursor the function returns is irreversible.
Parameter:
dfx |
Cellset file name |
… |
dfx’s parameter |
Option:
@c |
The dfx can be represented by a cell that corresponds to a subroutine defined by func() function |
Return value:
Cursor
Example:
Here’s test.dfx cellset file under the main directory in which arg1 is the cellset parameter:
|
A |
B |
|
1 |
for arg1 |
|
|
2 |
|
=connect("demo").query("select * from GYMNASTICSWOMEN where ID=?",A1) |
|
3 |
|
=B2.derive(avg(VAULT,UNEVENBARS,BALANCEBEAM,FLOOR):Average) |
|
4 |
|
return B3 |
Perform a loop computation and return the result sets |
|
A |
|
1 |
=[5,10,20,25] |
|
2 |
=cursor("test.dfx",A1) |
Cll test.dfx, pass A1’s sequence as the value of parameter arg1, and return result as a cursor |
3 |
=A2.fetch() |
Fetch records from the cursor; the result is the union of returned result sets by the loop computation of test.dfx |
4 |
=call("test.dfx",A1) |
Use call function to call test.dfx but only the result set of the first loop is returned |
|
A |
B |
|
1 |
func |
|
|
2 |
|
return A1.sum() |
|
3 |
=cursor@c(A1,[2,5,7]) |
|
Call the code block whose master cell is A1 |
4 |
=A3.fetch() |
|
14 |
Description:
Define a channel for each cursor according to a specified cursor.
Syntax:
cursor cs
cursor
…
cursor
Note:
The statement defines a channel for each cursor according to cursor cs (the value of the cursor cell is a channel), traverses cs to perform a calculation over the channel in the code block and write the result into the cell of cursor.
Parameter:
cs |
A cursor |
… |
A code block |
Return value:
Result set of channel
Example:
|
A |
B |
|
1 |
=to(100000).new(rand(1000):f1,rand(1000):f2).cursor() |
|
Generate a cursor |
2 |
cursor A1 |
=A2.groups(f1:count(1)) |
Traverse cursor A1, calculate A2’s channel in B2’s code block and write the result to A2 |
3 |
cursor |
=A3.groups(f2:count(1)) |
Same as the above |
Description:
Generate a cursor from a sequence.
Syntax:
A.cursor(k:n)
Note:
The function generates a cursor based on sequence A, divides the cursor data into n segments, and retrieves the kth segment.
Parameter:
A |
A sequence |
k |
Segment number |
n |
The number of data segments; retrieve all data out when both k and n are omitted |
Option:
@p |
Assume data is ordered by the first field and the division won’t put records having same first field to different segments |
Return value:
Cursor
Example:
|
A |
|
1 |
=demo.query("select * from SCORES") |
Return a sequence |
2 |
=A1.cursor(1:3) |
Generate a cursor using A1’s sequence, divide the cursor into three segments, and retrieve the 1st segment to return |
3 |
=A1.cursor() |
Generate a cursor using A1’s sequence; return all data of the cursor when parameters are absent |
4 |
=demo.query("select CLASS,STUDENTID,SUBJECT,SCORE from SCORES").sort(CLASS) |
Return a sequence ordered by the 1st field |
5 |
=A4.cursor@p(1:3) |
With @p option, put records having same Class value in one segment, during which records are not allocated strictly according to parameters k and n |
Description:
Generate a multicursor from a sequence.
Syntax:
A.cursor@m(n)
Note:
The function generates a multicursor using sequence A.
Parameter:
A |
A sequence |
n |
Number of subcursors; by default the value is【Default subcursor count in a multicursor】configured in the designer; when integrating esProc in a third-party application, its default value is cursorParallelNum value configured in raqsoftConfig.xml file |
Option:
@p |
Assume the sequence is ordered by the 1st field and records having same 1st field value won’t be put into two segments |
Return value:
Multicursor
Example:
|
A |
|
1 |
=demo.query("select CLASS,STUDENTID,SUBJECT,SCORE from SCORES") |
Return a sequence |
2 |
=A1.cursor@m() |
Generate a multicursor from A1’s sequence; as parameters are absent, use value of【Default subcursor count in a multicursor】as the default number of subcursors |
3 |
=demo.query("select CLASS,STUDENTID,SUBJECT,SCORE from SCORES").sort(CLASS) |
Return a sequence ordered by the 1st field |
4 |
=A1.cursor@mp(3) |
Generate a multicursor having three subcursors; with @p option, the segmentation won’t put records having same CLASS value in two segments |
Description:
Generate a multicursor segmented synchronously as an existing multicursor using a specified sequence.
Syntax:
A.cursor@m(mcs,K:K‘,...)
Note:
The function divides ordered sequence A into multiple segments according to multicursor mcs and returns a multicursor. Parameters K and K’ are segmentation keys of A and mcs respectively.
Parameter:
A |
An ordered sequence |
mcs |
A multicursor |
K |
A’s segmentation key |
K’ |
mcs’s segmentation key |
Option:
@p |
Ignore parameters K and K’ and divide ordered sequence A by comparing and matching the first fields of mcs and A |
Return value:
Multicursor
Example:
|
A |
|
1 |
=to(10000).new(#:c1,rand():c2).sort(c1) |
Generate a sequence |
2 |
=A1.cursor@m(3) |
Generate a multicursor having three subcursors from A1’s table sequence |
3 |
=to(10000).new(#:k1,rand(10000):k2,rand()*1000:k3).sort(k1) |
Generate a table sequence ordered by k1 |
4 |
=A3.cursor@m(A2,k1:c1) |
Generate a multicursor segmented synchronously as A2’s multicursor, whose segmentation key is c1, based on A3’s table sequence, whose segmentation key is k1 |
5 |
=A3.cursor@mp(A2) |
Generate a multicursor segmented synchronously as A2’s multicursorbased on A3’s table sequence; as @p option is present, perform the segmentation by comparing and matching the first fields of A3 and A2, and in this case segmentation key parameters are not needed in the expression |
Description:
Create a database cursor by executing an SQL statement.
Syntax:
db. cursor(sql {,args …}) |
|
Note:
The function creates a database cursor by executing SQL. The cursor will be automatically closed after a full data scan.
Parameter:
db |
Database connection |
sql |
A SQL query statement |
args |
Parameters passed to SQL, which can be values or names defined; separate multiple parameters with comma
|
Option:
@i |
If the result set has only one column, the content of the returned cursor is a sequence |
@d |
Convert the numeric data type to the double data type, instead of the decimal data type |
@x |
Disconnect from the database automatically when the cursor is closed; this option applies to connect -mode database connection only; with this option, the cursor the function returns is irreversible |
@v |
Generate a cursor of pure table sequence or pure sequence |
Return value:
A cursor
Example:
|
A |
|
1 |
=demo.cursor("select * from SCORES") |
Parameter db is data source name; this requires that demo data source is already connected |
2 |
=connect("demo") |
|
3 |
=A2.cursor@x("select * from SCORES").fetch() |
With connect-mode database connection, @x is used |
4 |
=A2.cursor("select * from STUDENTS") |
Error is reported: Data Source demo is shutdown or wrong setup., which prompts you that data source isn’t started as database is automatically disconnected when A3’s cursor is closed |
When the SQL query statement contains parameters:
|
A |
|
1 |
>arg2="R&D" |
Define parameter name as arg2 and parameter value as "R&D" |
2 |
=demo.cursor("select EID,NAME,DEPT,GENDER from employee where EID<? and DEPT=? and GENDER=?",arg1,arg2,"M") |
Query employee table, where arg1 is a cellset parameter whose value is 100 and arg2 is a parameter defined in A1, and the 3rd parameter receives value “M” directly |
3 |
=A2.fetch() |
Fetch data from A2’s cursor |
Return cursor containing other types of data:
|
A |
|
1 |
=demo.cursor("select NAME from STUDENTS") |
Query NAME field of STUDENTS table from demo data source and return a cursor whose content is as follows: |
2 |
=demo.cursor@i("select NAME from STUDENTS") |
When the result set contains only one column, use @i option to return content of the cursor as a sequence |
3 |
=demo.cursor@v("select * from STUDENTS") |
Return a cursor of pure table sequence |
4 |
=demo.cursor@iv("select NAME from STUDENTS") |
Return a cursor of pure sequence |
5 |
=mysql.cursor@d("select * from ta") |
As @d option is present, numeric data is converted to double type |
Related functions:
Description:
Create a cursor based on a file.
Syntax:
f.cursor() |
f.cursor(Fi:type,…; k:n,s) |
Note:
The function creates a cursor based on file f and returns it. The cursor will be automatically closed after a full data scan.
Parameter:
f |
File object, only supporting the textual file object |
Fi |
Fields to be retrieved; all fields will be retrieved by default. The to-be-retrieved field(s) can be represented by their ordinal numbers headed by the sign # |
type |
Field types, including bool, int, long, float, decimal, string, date, time and datetime. Data type of the first row will be used by default |
s |
User-defined separator. The default is tab. When the parameter is omitted, the comma preceding it can be omitted, too |
k |
The segment number |
n |
The number of segments. Retrieve the whole file when both k and n are omitted |
Option:
@t |
Use the first row of f as the field names. If omitted, use_1,_2,… as the default field names |
@b |
Retrieve data from the exported binary file, with the support for parameters Fi, k and n, and with no support for parameters type and s. Ignore options @t, @s, @i, @q and @m. The segmental retrieval could result in empty segment in cases when a file has only a very small number of records |
@e |
Make the function return null when Fi doesn’t exist in the file; raise an error when the option is absent |
@x |
Delete the source file automatically on closing the cursor; the cursor that the function returns is irreversible when this option works |
@s |
Not split the to-be-retrieved field when it is imported as a cursor whose content is a table sequence consisting of strings of a single field; in this case the parameters will be ignored |
@i |
If the result set has only one column, the content of the returned cursor will be a sequence |
@q |
Remove the quotation marks, if any, from the field strings, including the field names, in the first place, and handle escape sequences |
@c |
Use comma as the separator when the parameter s is absent, but the user-defined separator s should take priority when there is one |
@m |
With the option, the f.cursor@m(Fi:type,…;n,s) function returns a multicursor; here parameter n is the number of segments; use the value of 【Default subcursor count in a multicursor】defined in the designer as the number subcursors if the option is absent; when integrating esProc into a third-party application, use cursorParallelNum value configured in raqsoftConfig.xml file as the default |
@o |
Use quotation marks as the escape character |
@k |
Retain the white space on both sides of the data item; without it a trim operation will be automatically performed |
@d |
Delete a record if it contains unmatching data types or data formats and start examining data by type, or if the parentheses and the quotation marks in it do not match when @p option and @q option respectively are present |
@n |
Ignore and discard rows whose number of columns don’t match the first row |
@v
@w |
Throw an exception, terminate the execution and output the content of the problem record when errors appear in @d check and @n check Read each row, including the column headers row, as a sequence and return a cursor of sequence made up of sequences |
@a |
Treat single quotes as quotes, including scenarios where the option cooperates with @q option; single quotes are left not handled when both options are absent |
@p |
Enable handling the matching of parentheses (not including the separators within the parentheses) and quotes, as well as the escape sequences outside of the quotes |
@f |
Split the file content into a string by the separator without parsing |
@l |
Allow line continuation if there is an escape character \ at the end of the line |
Return value:
A cursor/multicursor
Example:
|
A |
B |
C |
|
|
1 |
=file("D://Student.txt").cursor@tx() |
Return the cursor for retrieving data, take the record in the first row as field names, and delete the file automatically when closing the cursor. |
|||
2 |
=create(CLASS,STUDENTID,SUBJECT,SCORE) |
Construct a new table sequence |
|||
3 |
for |
|
|
||
4 |
|
if A3==1 |
=A1.skip(5) |
If the number of loop is 1, then skip 5 consecutive rows. |
|
5 |
|
=A1.fetch(3) |
Retrieve data from cursorA1, 3 rows each time |
||
6 |
|
if B5==null |
Jump out from the loop when B5 is null |
||
7 |
|
|
Break |
|
|
8 |
|
else |
|
|
|
9 |
|
|
>A2.insert(0:B5,CLASS,STUDENTID,SUBJECT,SCORE) |
Insert records in B5 into A2 |
|
10 |
=file("D://Department.txt").cursor@t(Dept,Manager;,"/") |
=A10.fetch() |
Below is Department.txt: Contents of Department. txt are separated with the slashes and read out according to the specified fields of DEPT and MANAGER |
||
11 |
=file("D://Department5.txt").cursor@t(;1:2) |
=A11.fetch() |
With Fi and s omitted, the function separates the cursor into 2 segments and retrieves the first one |
||
12 |
=file("D:// EMPLOYEE. btx").cursor@b(GENDER;1:2) |
=A12.fetch() |
Retrieve the GENDER field of the bin file, EMPLOYEE.btx(a segmented binary file) exported through f.export@z(); by default the exported binary file includes field names . |
||
13 |
=file("D://EMPLOYEE.btx").cursor@b(;1:2) |
=A13.fetch() |
Retrieve the segmented binary file, EMPLOYEE1.btx, exported by f.export@b(), divide the file contents into 2 parts, and get the first part |
||
14 |
=file("D://Department.txt").cursor@ts() |
= A14.fetch() |
Won’t split fields, the cursor contains a table sequence consisting of a single string field |
||
15 |
=file("D://StuName.txt").cursor@i() |
=A15.fetch() |
StuName.txt is a file containing only one field, so the content of the cursor is a sequence |
||
16 |
=file("D://EMPLOYEE1.txt").cursor@tc() |
=A16.fetch() |
|
||
17 |
=file("D://Department3.txt").cursor@e(EID) |
=A17.fetch() |
Return null since EID field can’t be found in Department3.txt; without @e option, error will be reported, saying EID: field is not found |
||
18 |
=file("D://Department2.txt").cursor@tq(;,"|") |
=A18.fetch() |
Below is Department2.txt: B19’s result: |
||
19 |
=file("D://Department.txt").cursor@tm(DEPT:string,MANAGER:int;3,"/") |
|
The cursor is devided into 3 segments and the result of A19 is returned as a multicursor |
||
20 |
=file("D://Sale1.txt").cursor(#1,#3) |
=A20.fetch() |
Below is the Sale1.txt file:
|
||
21 |
=file("D:/Dep3.txt").cursor@cqo() |
=A21.fetch() |
With @o option, two double quotation marks are treated as one and return the result as follows: |
||
22 |
=file("D:/Dep1.txt").cursor@k() |
=A22.fetch() |
Retain the whitespace characters on both sides of the data item
|
||
23 |
=file("D:/Department1.txt").cursor@t(id:int,name;,"|") |
=A23.fetch() |
Return id and name fields of Department1.txt |
||
24 |
=file("D:/Department1.txt").cursor@td(id:int,name;,"|") |
=A24.fetch() |
Delete a record containing unmatching data types; rows where id value is a are deleted |
||
25 |
=file("D:/Department1.txt").cursor@tv(id:int,name;,"|") |
=A25.fetch() |
Verify data type matching, and, and if error reports, throw an exception, terminate the execution and output the content of the problem record; data type doesn’t match with rows where id value is a
|
||
26 |
=file("D:/Dep2.txt").cursor@tdn(id:int,name,surname;,"|") |
=A26.fetch() |
Here’s
the file Dep2.txt: Ignore row 6 and row 8 because the number of columns don’t match that in row 1 |
||
27 |
=file("D:/Desktop/DemoData/txt/City.txt").cursor@w() |
=A27.fetch() |
Use @w option to read each row as a sequence and return a cursor of sequence whose members are sequences |
||
28 |
=file("D://t1.txt").cursor@c() |
=A28.fetch() |
Below is t1.txt: With @c option, use the comma as the default separator and return result as follows: |
||
29 |
=file("D://t1.txt").cursor@cp() |
=A29.fetch() |
With @p option, parentheses and quotation marks matching will be handled during parsing |
||
30 |
=file("D://t1.txt").cursor@cpa() |
=A30.fetch() |
With @a option, single quotes are treated as quotes |
||
31 |
=file("D://t2.txt").cursor@l() |
=A31.fetch() |
Below is t2.txt: With @1 option, allow line continuation when there is an escape character at the end of the line |
||
32 |
=file("D://t3.txt").cursor@f() |
=A32.fetch() |
With @f option, just splt the file as a string using the separator |
||
Related functions:
Description:
Segment an entity table or an in-memory table and return the cursor of a specified segment.
Syntax:
T.cursor(x:C,…;wi,…;k:n)
Note:
The function splits entity table/in-memory table T into n segments, computes data in the kth segment with expression x and the filtering condition wi, and returns the results as a cursor which contains a field named C. If T is an attached table, the queried data is allowed to include a parent table field.
Zone tables are segmented as a whole when T is a multi-zone composite table.
Option:
@m |
The T.cursor@m(x:C…;wi,...;n) function with this option generates a multicursor segmented into n parts. n is an integer; the function returns an ordinary cursor if n<2; use the value of【Default number of subcursors in a multicursor】 set in【Tool】-【Options】if n is absent |
@v |
Generate a pure table sequence-based column-wise cursor, which has higher performance than regular cursors |
@x |
Automatically close the entity table/in-memory table after data in the cursor is fetched |
@z |
When parameter T is a multi-zone composite table, merge zone tables, which must be synchronously segmented, by the dimension (and fields before it) |
Parameter:
T |
An entity table or an in-memory table |
x |
Expression |
C |
Column alias; can be omitted |
wi |
Filtering condition; separate multiple conditions by comma(s) and their relationships are AND; besides regular filtering expressions, you can also use the following five types of syntax in a filtering condition, where K is a field in the entity table: 1.K=w w is expression Ti.find(K) or Ti.pfind(K), where Ti is a table sequence. When value of w is null or false, the corresponding record in the entity table will be filtered away; when w is expression Ti.find(K) and the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K; when w is expression Ti.pfind(K) and the to-be-selected fields C,... contain K, ordinal numbers of K values in Ti will be assigned to K. 2.(K1=w1,…Kn=wn,w) wi is expression Ti.find(K) or Ti.pfind(K), where Ti is a table sequence. When value of wi is null or false, the corresponding record in the entity table will be filtered away; when wi is expression Ti.find(K) and the to-be-selected fields C,... contain K1,...Ki, T1,...Ti’s referencing field will be assigned to K1,...Ki correspondingly; when w is expression Ti.pfind(K) and the to-be-selected fields C,... contain K, ordinal numbers of K values in Ti will be assigned to K. w is an expression that returns a Boolean value; each of the multiple Ki=wi and w has a relationship of AND; you can reference Ki in w. 3.K:Ti Ti is a table sequence. Compare Ki value in the entity table with key values of Ti and discard records whose Ki value does not match; when the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K. 4.K:Ti:null Filter away all records that satisfy K:Ti. 5.K:Ti:# Locate records according to ordinal numbers, compare ordinal numbers of records in table sequence Ti according to the entity table’s K values, and discard non-matching records; when the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K.
|
k |
A positive integer (k≤n) representing the kth segment |
n |
A positive integer representing the number of segments |
Return value:
A single-thread cursor/multicursor
Example:
|
A |
|
1 |
for 100 |
|
2 |
=to(10000).new(#:k1,rand():c1).sort@o(k1) |
Return a table sequence |
3 |
=to(10000).new(#:k1,rand(10000):c2,rand()*1000:c3).sort@o(k1) |
Return a table sequence |
4 |
=A2.cursor() |
Return a cursor |
5 |
=A3.cursor() |
Return a cursor |
6 |
=file(“D:\\tb4.ctx”) |
Generate a composite table file |
7 |
=A6.create(#k1,c1) |
Create A6’s base table whose key is k1 |
8 |
=A7.append(A4) |
Append data of A4’s cursor to A7’s base table |
9 |
=A7.attach(table4,c2,c3) |
Create an attached table table4 for the base table |
10 |
=A9.append(A5) |
Append data of A5’s cursor to attached table table4 |
11 |
=A9.cursor(;c2<1000;2:3) |
Divide records meeting c2<1000 in the attached table into 3 segments and return a cursor of all columns of the second segment |
12 |
=A11.fetch() |
Fetch data from A11’s cursor |
13 |
=A7.cursor(;c1>0.99) |
Get records meeting c1>0.99 from A7’s base table |
14 |
=A13.fetch() |
Fetch data from A13’s cursor
|
15 |
=A9.cursor(k1,c1:b,c3;c3>999) |
Get the base table’s k1 field and c1 field from attached table table4, as well as c3 field of the attached table, according to condition c3>999, and rename c1 b |
16 |
=A15.fetch() |
Fetch data from A15’s cursor |
17 |
=A9.cursor@m(;;3) |
Use @m option to generate a multicursor from attached table table4 |
|
A |
|
1 |
=file(“employee1.ctx”) |
Generate composite table file employee1.ctx |
2 |
=A1.create@y(#EID,NAME,GENDER,SALARY) |
Create the base table of composite table employee1.ctx, which contains columns EID, NAME, GENDER and SALARY and where EID is the dimension |
3 |
=connect(“demo”).cursor(“select EID,NAME,GENDER,SALARY from employee”) |
Return a cursor |
4 |
=A2.append@i(A3) |
Append records of A3’s cursor to A2’s base table |
5 |
=A2.cursor@v(;SALARY>1000;) |
Return a column-wise cursor, and automatically close A3’s cursor after data is fetched from it |
6 |
=A5.groups(GENDER;avg(SALARY):SALARY_AVG) |
Perform grouping & aggregation operation on A5’s cursor |
7 |
=A2.cursor(;SALARY>2000) |
Error is reported, prompting“Stream Closed” |
When T is a multi-zone composite table
|
A |
|
1 |
=connect("demo").cursor("select EID,NAME,GENDER,SALARY from employee") |
|
2 |
=file("emp.ctx":[1,2]) |
Generate a homo-name files group |
3 |
=A2.create@y(#EID,NAME,GENDER,SALARY;if(GENDER=="F",1,2)) |
Return a multi-zone composite table |
4 |
=A3.append@x(A1) |
Append data in A1’s cursor to the multi-zone composite table |
5 |
=A3.cursor@z(EID,NAME,GENDER,SALARY;SALARY>5000;1:4) |
Divide A3’s multi-zone composite table into 4 segments and return the first segment, during which zone tables are merged by dimension field EID |
Use special types of filtering conditions:
|
A |
|
1 |
=file("emp.ctx") |
|
2 |
=A1.open() |
Open the composite table file |
3 |
=A2.import() |
As no parameters are present, return all data in the entity table |
4 |
=5.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence using ID as the key |
5 |
=A2.cursor(EID,NAME;EID=A4.find(EID)) |
Use K=w filtering mode; in this case w is Ti.find(K) and entity table records making EID=A4.find(EID) get null or false are discarded; EID is the selected field, to which table sequence A4’s referencing field are assigned |
6 |
=A2.cursor(EID,NAME;EID=A4.pfind(EID)) |
Use K=w filtering mode; in this case w is Ti.pfind(K) and entity table records making EID=A4.pfind(EID) get null or false are discarded; EID is the selected field, to which its ordinal numbers in table sequence A4 is assigned |
7 |
=A2.cursor(EID,NAME;EID:A4) |
Use K:Ti filtering mode; compare the entity table’s EID values with the table sequence’s key values and discard entity table records that cannot match |
8 |
=A2.cursor(NAME,SALARY;EID:A4) |
This is a case where K isn’t selected; EID isn’t the selected field, so only filtering is performed |
9 |
=A2.cursor(EID,NAME;EID:A4:null) |
Use K:Ti:null filtering mode; compare the entity table’s EID values with the table sequence’s key values and discard entity table records that can match |
10 |
=A2.cursor(EID,NAME;EID:A4:#) |
Use K:Ti:# filtering mode; compare with ordinal numbers of table sequence’s records according to the entity table’s EID values, and discard records that cannot match |
11 |
=connect("demo").query("select top 2 NAME,GENDER from employee").keys(NAME) |
Return a table sequence using NAME as the key |
12 |
=A2.cursor(EID,NAME;(EID=A4.find(EID),NAME=A11.find(NAME),EID!=null&&NAME!=null)) |
Use (K1=w1,…Kn=wn,w) filtring mode; return records that meet
all conditions |
Description:
Return the specified column(s) in a cluster entity table/cluster in-memory table as a cluster cursor.
Syntax:
T.cursor(x,…;wi,...)
Note:
The function computes data in a cluster entity table/cluster in-memory table with expression x and the filtering condition w and returns the results as a cluster cursor. By default, a duplicate table is distributed among nodes.
Option:
@m |
The function with this option generates a cluster multicursor synchronously segmented into n parts and splits a duplicate table on two levels. n is a positive integer representing the number of segments to be divided; the function returns an ordinary cursor if n<2; use the value of【Default number of subcursors in a multicursor】set in【Tool】-【Options】if n is absent. |
Parameter:
T |
A cluster entity table/cluster in-memory table |
x |
An expression |
wi |
Filtering condition; multiple conditions are separated by comma(s) and their relationships are AND |
Return value:
A cluster (multi)cursor
Example:
|
A |
|
1 |
=file(“D:/tb4.ctx”:[1],“169.254.121.62:8281”) |
Open a cluster table file |
2 |
=A1.open() |
Return a cluster composite table |
3 |
=A2.attach(table3) |
Retrieve cluster entity table table3 |
4 |
=A2.cursor() |
Return a cluster cursor |
5 |
=A3.cursor(NAME,GENDER;EID<6) |
Return NAME field and GENER field where EID<6 as a cluster cursor |
6 |
=file(“tb5.ctx”:[2],“192.168.31.72:8281”) |
|
7 |
=A6.open() |
|
8 |
=A7.cursor@m(;;3) |
Generate a cluster multicursor that is segmented into 3 parts |
9 |
=A7.cursor() |
|
10 |
=A9.memory() |
Generate a cluster in-memory table |
11 |
=A10.cursor() |
Return a cluster cursor |
Description:
Synchronously segment an entity table according to a multicursor and return a multicursor.
Syntax:
T.cursor(x:C,…;wi,...;mcs)
Note:
The function synchronously segments entity table T according to multicursor mcs, during which T’s first field and mcs’s first field will be matched, and returns a multicursor. The fields for segmenting T and mcs should be consistent. If multiple entity tables are involved, use a multicursor as a reference to make sure the segmentation is synchronous.
Parameter:
T |
An entity table |
x |
Expression |
C |
Column alias |
wi |
Filtering condition; retrieve the whole set when this parameter is absent; separate multiple conditions by comma(s) and their relationships are AND. Besides regular filtering expressions, you can also use the following five types of syntax in a filtering condition, where K is a field in the entity table: 1.K=w w is expression Ti.find(K) or Ti.pfind(K), where Ti is a table sequence. When value of w is null or false, the corresponding record in the entity table will be filtered away; when w is expression Ti.find(K) and the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K; when w is expression Ti.pfind(K) and the to-be-selected fields C,... contain K, ordinal numbers of K values in Ti will be assigned to K. 2.(K1=w1,…Kn=wn,w) wi is expression Ti.find(K) or Ti.pfind(K), where Ti is a table sequence. When value of wi is null or false, the corresponding record in the entity table will be filtered away; when wi is expression Ti.find(K) and the to-be-selected fields C,... contain K1,...Ki, T1,... Ti’s referencing field will be assigned to K1,...Ki correspondingly; when w is expression Ti.pfind(K) and the to-be-selected fields C,... contain K, ordinal numbers of K values in Ti will be assigned to K. w is an expression that returns a Boolean value; each of the multiple Ki=wi and w has a relationship of AND; you can reference Ki in w. 3.K:Ti Ti is a table sequence. Compare Ki value in the entity table with key values of Ti and discard records whose Ki value does not match; when the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K. 4.K:Ti:null Filter away all records that satisfy K: Ti. 5.K:Ti:# Locate records according to ordinal numbers, compare ordinal numbers of records in table sequence Ti according to the entity table’s K values, and discard non-matching records; when the to-be-selected fields C,... contain K, Ti’s referencing field will be assigned to K.
|
mcs |
A multicursor generated from an entity table |
Option:
@k |
Perform matching using the multicursor’s first field of the key |
@v |
Generate a pure table sequence-based column-wise cursor, which has higher performance than regular cursors |
@x |
Automatically close the entity table after data is fetched from the cursor |
@z |
When parameter T is a multi-zone composite table, merge zone tables, which must be synchronously segmented, by the dimension (and fields before it) |
Return value:
A multicursor
Example:
|
A |
|
1 |
for 100 |
|
2 |
Return a table sequence |
|
3 |
=to(10000).new(#:k1,rand(10000):c2).sort@o(k1) |
Return a table sequence |
4 |
=to(10000).new(#:k1,rand()*1000:c3).sort@o(k1) |
Return a table sequence |
5 |
=A2.cursor() |
Return a cursor |
6 |
=A3.cursor() |
Return a cursor |
7 |
=A4.cursor() |
Return a cursor |
8 |
=file("D:\\cs1.ctx") |
Generate a composite table file |
9 |
=A8.create(#k1,c1) |
Create the composite table’s base table |
10 |
=A9.append(A5) |
Append data in A5’s cursor to the base table |
11 |
=A9.attach(table1,c2) |
Create attached table table1 on base table |
12 |
=A11.append(A6) |
Append data in A6’s cursor to table1 |
13 |
=A11.cursor@m(;;2) |
Segment the attached table and return a multicursor |
14 |
=A9.attach(table2,c3) |
Create attached table table2 on base table |
15 |
=A14.append(A7) |
Append data in A7’s cursor to table2 |
16 |
=A14.cursor@v(;;A13) |
Segment table2 according to A13’s multicursor |
|
A |
|
1 |
=connect("demo").cursor("select EID,NAME from employee") |
|
2 |
=file("emp.ctx") |
Generate a composite table file |
3 |
=A2.create@y(#EID,NAME) |
Create the composite table’s base table |
4 |
=A3.append(A1) |
Append data in A1’s cursor to the composite table’s base table |
5 |
=A4.cursor@m(;;3) |
Generate a multicursor having 3 subcursors according to the composite table |
6 |
=connect("demo").cursor("select EID,NAME,GENDER,SALARY from employee") |
|
7 |
=file("emp.ctx":[1,2]) |
Generate a homo-name files group |
8 |
=A7.create@y(#EID,NAME,GENDER,SALARY;if(GENDER=="F",1,2)) |
Create a multi-zone composite table |
9 |
=A8.append@x(A6) |
Append data in A6’s cursor to the composite table’s base table |
10 |
=A8.cursor@z(EID,NAME,GENDER,SALARY;SALARY>5000;A5) |
Segment A8’s multi-zone composite table according to A5’s multicursor and merge zone tables by dimension field EID |
Use special types of filtering conditions:
|
A |
|
1 |
=file("emp.ctx") |
|
2 |
=A1.open() |
Open the composite table file |
3 |
=A2.import() |
As no parameters are present, return all data in the entity table |
4 |
=file("emp1.ctx").open().cursor@m(;;2) |
Return a multicursor |
5 |
=5.new(~:ID,~*~:Num).keys(ID) |
Generate a table sequence using ID as the key |
6 |
=A2.cursor(EID,NAME;EID=A5.find(EID);A4) |
Use K=w filtering mode; in this case w is Ti.find(K) and entity table records making EID=A4.find(EID) get null or false are discarded; EID is the selected field, to which table sequence A5’s referencing field is assigned |
7 |
=A2.cursor(EID,NAME;EID=A5.pfind(EID);A4) |
Use K=w filtering mode; in this case w is Ti.pfind(K) and entity table records making EID=A4.pfind(EID) get null or false are discarded; EID is the selected field, to which its ordinal numbers in table sequence A5 are assigned |
8 |
=A2.cursor(EID,NAME;EID:A5;A4) |
Use K:Ti filtering mode; compare the entity table’s EID values with the table sequence’s key values and discard entity table records that cannot match |
9 |
=A2.cursor(NAME,SALARY;EID:A5;A4) |
This is a case where K isn’t selected; EID isn’t the selected field, so only filtering is performed |
10 |
=A2.cursor(EID,NAME;EID:A5:null;A4) |
Use K:Ti:null filtering mode; compare the entity table’s EID values with the table sequence’s key values and discard entity table records that can match |
11 |
=A2.cursor(EID,NAME;EID:A5:#;A4) |
Use K:Ti:# filtering mode; compare with ordinal numbers of table sequence’s records according to the entity table’s EID values, and discard records that cannot match |
12 |
=connect("demo").query("select top 2 NAME,GENDER from employee").keys(NAME) |
Return a table sequence using NAME as the key |
13 |
=A2.cursor(EID,NAME;(EID=A5.find(EID),NAME=A12.find(NAME),EID!=null&&NAME!=null);A4) |
Use (K1=w1,…Kn=wn,w) filtring mode; return records that meet all conditions |
Description:
Synchronously segment a cluster entity table according to a cluster multicursor and return a cluster multicursor.
Syntax:
T.cursor(x;C,…;wi;mcs)
Note:
The function synchronously segments cluster entity table T according to cluster multicursor mcs, during which T’s dimension and mcs’s key will be matched, and returns a cluster multicursor; the way of distributing T and mcs among nodes must be the same and the fields for segmenting T and mcs should be consistent. If multiple cluster entity tables are involved, use a cluster multicursor as a reference to make sure the segmentation is synchronous.
Parameter:
T |
A cluster entity table |
x |
Expression |
wi |
Filtering condition; retrieve the whole set when this parameter is absent; separate multiple conditions by comma(s) and their relationships are AND |
mcs |
A cluster multicursor generated from a cluster entity table |
Return value:
A cluster multicursor
Example:
|
A |
|
1 |
=file("cs1.ctx":[1],"169.254.121.62:8281") |
|
2 |
=A1.open() |
Open a cluster composite table |
3 |
=A2.attach(table1) |
Retrieve cluster entity table table1 |
4 |
=A3.cursor@m(;;2) |
Return a cluster multicursor |
5 |
=A2.attach(table2) |
Retrieve cluster entity table table2 |
6 |
=A5.cursor(;;A4) |
Segment cluster entity table table2 according to table1’s multicursor; the two tables should use same segmenting field |
Description:
Get a cursor based on a pseudo table object.
Syntax:
T.cursor(xi:Ci,…)
Note:
The function gets a cursor based on pseudo table T by specifying field expressions xi and field names Ci, which, by default, field names in the pseudo table. It gets a cursor using all fields of the pseudo table when paramters xi:Ci are absent.
Parameter:
T |
A pseudo table |
xi |
A field expression |
Ci |
Field name in a result table sequence |
Option:
@v Store the composite table in the column-wise format when loading it the first time, which helps to increase performance
Return value:
A cursor
Example:
|
A |
|
1 |
=create(file).record(["D:/file/pseudo/empT.ctx"]) |
|
2 |
=pseudo(A1) |
Generate a pseudo table object |
3 |
=A2.cursor() |
Generate a cursor from A2’s pseudo table using all its fields, since no parameters are present |
4 |
=A3.fetch() |
Below is data in A3’s cursor: |
5 |
=A2.cursor(EID:eid,NAME,SALARY:salary) |
Retrieve fields EID, NAME and SALARY from the pseudo table to generate a cursor with new field names eid, NAME and salary respectively |
6 |
=A5.fetch() |
Below is data in A5’s cursor: |
Description:
Merge subcursors in a multicursor into a single cursor or a new multicursor with a smaller number of parallel subcursors.
Syntax:
mcs.cursor(n)
Note:
The function merges subcursors in a multicursor into a single cursor, which is the default, or a new multicursor with a smaller number, which is specified by parameter n, of parallel subcursors.
Parameter:
mcs |
A multicursor |
n |
Subcursor column name |
Return value:
A common cursor or a multicursor
Example:
|
A |
|
1 |
=demo.query("select * from EMPLOYEE").cursor@m(5) |
Return a multicursor having 5 subcursors |
2 |
=A1.cursor(3) |
Merge subcursors in the multicursor and convert it to a 3-subcursor multicursor |
3 |
=A2.cursor() |
Convert a multicursor to a single cursor |