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

Read(215) Label: key value, field-retrieving,

Description:

Get values of specified field(s) from a composite table according to key values in a table sequence, record sequence or a cursor and return result as a table sequence or a cursor.

Syntax:

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

Note:

 The function retrieves values of field(s) x,... from composite table T according to key values in table sequence/record sequence A or in cursor cs. The table sequence, record sequence and cursor should be ordered by the key, and their key should keep the same order as the existing key field(s) in the composite table.

primary tableWithout @r option, the function supports an aggregate operation. In this case, T should be the primary table and should be the subtable, on which an aggregation is performed.

 

Parameters:

T

A composite table

A/cs

A table sequence/cursor

x

Field values

C

Column alias; can be absent

wi

Filtering condition; separate multiple conditions, which should all be met, by comma(s)

Options:

@r

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

@z

Match existing fields of A/cs with T’s dimension/key

Return value:

A table sequence/cursor

Example:

 

A

 

1

=file("D:\\emp10.ctx").open()

Open a composite table

2

=demo.query("select EID from EMPLOYEE order by EID").keys(EID)

Return a table sequence ordered by primary key EID in ascending order

 

3

=A1.new(A2,NAME,GENDER)

Retrieve other fields of the composite table according to table sequence A2’ key values

 

4

=demo.query("select * from EMPLOYEE order by EID").keys(EID).cursor()

 

 

 

5

=A1.new(A4,NAME:Name,GENDER:Gender).fetch()

Retrieve other fields of the composite table according to cursor A4’ key values

6

=file("D:/employees.ctx").open()

Open a composite table. It’s data is as follows:

7

=demo.query("select EID from employee where EID<10 ").keys(EID)

Return a table sequence

8

=A6.new(A7,EID,Gender,Dept;Gender=="F",Dept=="Sales")

Retrieve fields where Gender=="F" & Dept=="Sales" from A7’s composite table by the key EID

9

=file("D:/file/dw/sale.ctx").open()

Below is the composite table:

10

=demo.query("select OrderID as ID,Amount as amount from OrderDetail where OrderID<10251").keys(ID)

Return table sequence as follows:

11

=A9.new@r(A10,ID,(sum(AMOUNT)):TOTAL)

Match records of composite table A9 according to table sequence A10’s key values to get field values of the composite table and perform aggregation. Here’s the result:

12

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

Open a composite table whose key is EID

13

=demo.query("SELECT EMPLOYEEID,EVALUATION,BONUS FROM PERFORMANCE")

Return a table sequence

14

=A12.new@z(A13,EID,NAME,GENDER,EVALUATION)

Use @z option to match existing fields of A18 with the key of A17’s composite table