C

Read(2372) Label: composite table, column,

Here are operations related to composite table colomns.

C

Description:

A column in a table, its parent table, or a table above them.

Note:

It is a column in a table, its parent table, or a higher level parent table; can be referenced directly by name without being prefixed by the table name. For example, if table A1 is table B1’s parent table, then a column of table A1 can be directly referenced by name from table B1, instead of using a format like A1.C.

 

Note: If one of table B1’s columns is referenced from table A1, the format should be B1.C.

Example:

 

A

 

1

=create(k1,v1).record([1,10,2,20,3,30,4,40,10,100]) .sort(k1)

Generate a table sequence:

2

=create(k1,k2,v2).record([1,1,"a",2,1,"c"])

Generate a table sequence:

3

=file("D:/ctb.ctx")

 

4

=A3.create(#k1,v1)

Create the composite table’s primary table

5

=A4.attach(table2,#k2,v2)

Add subtable table2 to the primary table.

6

=A4.append(A1.cursor())

 

7

=A5.append(A2.cursor())

 

8

=A4.cursor(k1,v1,table2.v2).fetch()

Reference column v2 of the subtable table2 from its parent table.

9

=A5.cursor(k1,v1,v2).fetch()

Reference columns k1 and v1 of the parent table from the subtable.

T.C

Description:

Get a specified column from an attached table.

Note:

Get the value of column C from attached table T. Its value will be recorded as nulls if the current record doesn’t have column C. If the value of column C corresponds to multiple records, only get the first record.

Parameter:

T

An attached table

C

A column of the attached table

Example:

 

A

 

1

=create(k1,v1).record([1,10,2,20,3,30,4,40,10,100])

Create a table sequence:

2

=create(k1,k2,v2).record([1,1,"a",2,4,"c",2,8,"b"])

Create a table sequence:

3

=file("D:/ckv.ctx")

 

4

=A3.create(#k1,v1)

Create the composite table’s base table.

5

=A4.attach(table2,#k2,v2)

Add an attached table to the base table.

6

=A4.append(A1.cursor())

Append data to the base table.

7

=A5.append(A2.cursor())

Append data to the attached table.

8

=A4.cursor(k1,v1,table2.k2,table2.v2).fetch()

Retrieve columns k1 and v1 from the composite table’s base table, and columns k2 and v2 from attached table table2; record values of k2 and v2 as empty if a record doesn’t have the two fields. Get the first record if there are multiple records in the attached table that correspond to one value.

T.f(C)

Description:

Perform an aggregate operation over an attached table’s column.

Syntax:

T.f(C)

Note:

The function performs an aggregation (function f) over the multiple records corresponding to one value in a specific column of an attached table.

Parameter:

T

An attached table

f

An aggregate operation performed over the attached table; support count, sum, max, min, avg, top and iterate

C

An attached table’s column

Example:

 

A

 

1

=create(k1,v1).record([1,10,2,20,3,30,4,40,10,100])

Create a table sequence:

2

=create(k1,k2,v2).record([1,1,"a",2,4,"c",2,8,"b"])

Create a table sequence:

3

=file("D:/ckv.ctx")

 

4

=A3.create(#k1,v1)

Create the composite table’s base table.

5

=A4.attach(table2,#k2,v2)

Add an attached table to the base table.

6

=A4.append(A1.cursor())

Append data to the base table.

7

=A5.append(A2.cursor())

Append data to the attached table.

8

=A4.cursor(k1,table2.sum(k2):k2_sum).fetch()

Perform a sum operation over each value of column k2 in attached table table2, and name the new column k2_sum.

9

=A4.cursor(table2.count(k2)).fetch()

Perform count operation over each value of column k2 in attached table table2.

10

=A4.cursor(table2.max(k2)).fetch()

Perform max operation over each value of column k2 in attached table table2.

11

=A4.cursor(table2.min(k2)).fetch()

Perform min operation over each value of column k2 in attached table table2.

12

=A4.cursor(table2.avg(k2)).fetch()

Perform average operation over each value of column k2 in attached table table2.

13

=A5.cursor().top(2;k2)

Return the records corresponding to the first two smallest values in column k2 in entity table table2.

14

=A5.cursor().derive(iterate(~~*2,10):F1).fetch()

Perform an iteration over entity table table2.

T{x:C,…}

Description:

Return an attached table’s records as a sub table sequence.

Syntax:

T{x:C,…}

Description:

The function returns an attached table’s records that match the current primary key value as a sub table sequence.

Parameter:

T

An attached table

x

An attached table’s column

C

Column alias; can be omitted

Example:

 

A

 

1

=create(k1,v1).record([1,10,2,20,3,30,4,40,10,100])

Create a table sequence:

2

=create(k1,k2,v2).record([1,1,"a",2,4,"c",2,8,"b"])

Create a table sequence:

3

=file("D:/ckv.ctx")

 

4

=A3.create(#k1,v1)

Create the composite table’s base table.

5

=A4.attach(table2,#k2,v2)

Add an attached table to the base table.

6

=A4.append(A1.cursor())

Append data to the base table.

7

=A5.append(A2.cursor())

Append data to the attached table.

8

=A4.cursor(k1,table2{k2,v2}:t2).fetch()

Return records of attached table table2 as a table sequence.