T.new(A/cs:K…,x:C,…;wi)

T

A composite table

A/cs

A table sequence/cur sor

K

Name of the A/cs’s field that corresponds to T’s key (/dimension) Field of A/cs; when it is specified, use it to match with T’s key (/dimension); when it is absent, use the first field of A/cs to perform the matching; when there are multiple  Ks, use colon (:) to separate them

x

Field expression or aggregate function count/sum/max/min/avg

C

Column alias; can be absent

wi

Filtering condition on T; 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 non-key field in entity table T:

1．K=w

w usually uses expression T_i.find(K) or T_i.pfind(K), where T_i 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 T_i.find(K) and the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K; when w is expression T_i.pfind(K) and the to-be-selected fields C,... contain K, sequence numbers of K values in T_i will be assigned to K.

2．(K₁=w₁,…K_i=w_i,w)

K_i=w_i is an assignment expression. Generally, parameter w_i can use expression T_i.find(K_i) or T_i.pfind(K), where T_i is a table sequence; when w_i is expression T_i.find(K_i) and the to-be-selected fields C,... contain K_i, T_i’s referencing field will be assigned to K_i correspondingly; when w_i is expression T_i.pfind(K_i) and the to-be-selected fields C,... contain K_i, sequence numbers of K_i values in T_i will be assigned to K_i.

w is a filter expression; you can reference K_i in w.

3．K:T_i

T_i is a table sequence. Compare K_i value in the entity table with key values of T_i and discard records whose K_i value does not match; when the to-be-selected fields C,... contain K, T_i’s referencing field will be assigned to K.

4．K:T_i:null

Filter away all records that satisfy K:T_i

@r

Copy records of the primary table and return result set aligned by A/cs

A

1

=connect("demo").cursor("select  STATEID,CAPITAL from STATECAPITAL")

Return a cursor.

2

=file("spec-new.ctx")

3

=A2.create@y(#STATEID,CAPITAL)

Create a composite table and set STATEID as the key.

4

=A3.append@i(A1)

Append data in cursor A1 to the composite table; below is content of the result table:

5

=demo.query("select  STATEID,CID,NAME,POPULATION from CITIES").sort(STATEID)

Return a table sequence:

6

=A4.new(A5:STATEID,CAPITAL,sum(POPULATION):STA_POP)

A4 is the primary table and A5 is the subtable; perform the matching between the composite table’s key STATEID and the table sequence’s STATEID field and return a table sequence consisting of CAPITAL field and STA_POP field whose contents are results of performing an aggregate operation on the subtable’s POPULATION field:

7

=A4.new(A5,CAPITAL,sum(POPULATION):STA_POP)

As parameter K is absent, perform matching between the composite table’s key and the table sequence’s first field – STATEID and return same result as A6.

8

=A4.new@r(A5:STATEID,CAPITAL,NAME,POPULATION)

Use @r option to copy the primary table records and return a result set aligned to the subtable:

9

=A4.new@r(A5:STATEID,STATEID,CAPITAL,NAME,POPULATION;STATEID<3,left(CAPITAL,1)=="M")

Filter A4’s data according to filter condition – STATEID<3 and the first letter of CAPTIAL value is M – during the association:

A

1

=file("stu.ctx").open()

Open a composite table; its content is as follows:

2

=file("sco.txt").import@t()

Return a table sequence as follows:

3

=A1.new(A2:Class:StudentID,NAME,sum(SCORE):TotalScore)

A1 is the primary table and A2 is the subtable; perform the matching between the composite table’s key and the table sequence’s Class and StudentID and return a table sequence consisting of NAME field and TotalScore field, whose contents are results of performing aggregation on subtable’s SCORE field:

4

=A1.new@r(A2:Class:StudentID,NAME,SUBJECT,SCORE)

Use @r option to copy the primary table records and return a result set aligned to the subtable:

A

1

=demo.cursor("select * from STUDENTS")

Below is content of the cursor:

2

=file("students.ctx")

3

=A2.create@y(#ID,NAME,GENDER,AGE)

Create a composite table.

4

=A3.append@i(A1)

Append cursor A1’s data to the composite table’s base table.

5

=demo.query("select top 12 STUDENTID,SUBJECT,SCORE  from SCORES")

Return a table sequence:

6

=A3.new(A5:STUDENTID,ID,NAME,AGE)

Match the composite table’s key values to table sequence’s fields and retrieve corresponding composite table fields and return a table sequence:

7

=create(NAME,Num).record(["Emily",12,"Lauren",45]).keys(NAME)

Generate a table sequence using NAME as the key:

8

=A3.new(A5:STUDENTID,ID,NAME,AGE;NAME=A7.find(NAME))

Use K=w filtering mode; in this case w is T_i.find(K) and entity table records making NAME=A7.find(NAME) get null or false are discarded; NAME is the selected field, to which table sequence A7’s referencing field is assigned.

9

=A3.new(A5:STUDENTID,ID,NAME;NAME=A7.pfind(NAME))

Use K=w filtering mode; in this case w is T_i.pfind(K) and entity table records making NAME=A7.find(NAME) get null or false are discarded; NAME is the selected field, to which its sequence numbers in table sequence A7 are assigned.

10

=A3.new(A5:STUDENTID,ID,NAME;NAME:A7)

Use K:T_i filtering mode; compare the entity table’s NAME values with the table sequence’s key values and discard entity table records that cannot match.

11

=A3.new(A5:STUDENTID,ID,GENDER;NAME:A7)

This is a case where K isn’t selected; NAME isn’t the selected field, so only filtering is performed.

12

=A3.new(A5:STUDENTID,ID,NAME;NAME:A7:null)

Use K:T_i:null filtering mode; compare the entity table’s NAME values with the table sequence’s key values and discard entity table records that can match.

13

=create(Age,Chinese_zodiac_sign).record([14,"tiger",15,"ox",16,"rat"]).keys(Age)

Return a table sequence using AGE as the key.

14

=A3.new(A5:STUDENTID,ID,NAME,AGE;(AGE=A13.find(AGE),NAME=A7.find(NAME),AGE!=null&&NAME!=null))

Use (K₁=w₁,…K_i=w_i,w) filtering mode; return records that meet all conditions.