news()

Read(257) Label: news,

Here’s how to use news() function.

A.news( X;xi:Fi,… )

Description:

Compute field values based on a table sequence/record sequence, and concatenate them to generate a new one. 

Syntax:

A.news(X;xi:Fi,…)

Note:

The function computes field values based on record sequence X, concatenates the newly-computed fields to create a new table sequence/record sequence where parameter xi is field value of each record and Fi is the field name, which will be automatically identified according to parameter xi if Fi is omitted. When parameter xi is #i, it represents the ith field and the original field name will be used.

Parameters:

A

A table sequence/record sequence

X

A record sequence/integer; equivalent to to(X) if it is an integer, meaning the frequency of inserting each record in A

xi

Expression, whose results will be field values; the sign ~ used in the parameter references data from record sequence X instead of A. The sign # is used to represent a field with a sequence number

Fi

Field name of the new table sequence/record sequence; when omitted, it will be automatically identified

Options:

@1

Left join, which creates an empty record when record sequence X is empty

Return value:

A new table sequence/record sequecne

Example:

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.group(GENDER;~:group,~.avg(SALARY):avg)

Perform group by GENDER

3

=A2.news(group;EID,NAME,GENDER, #3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,A2.avg:AvgSalary)

A2 is composed of data groups; based on each group field values are computed and a table sequence is generated; and then table sequences are concatenated into a new one.

4

=A1.news(2;EID,NAME,GENDER,STATE,age(A1.BIRTHDAY):AGE)

Generate a new table sequence by inserting each record twice

5

=A1.group(DEPT).(~.group(GENDER))

 

Group records first by DEPT then by GENDER

6

=A5.news(~;A5.~.DEPT:DEPT,A5.~.~.GENDER:GENDER,A5.~.~.avg(SALARY):AvgSalary)

1559034619(1)

Get DEPT values and GENDER values from A5 to show the average salaries of different GENDERs in every DEPT

7

=MySQL1.query("select * from EMPLOYEE")

 

8

=A7.group(GENDER;~:group)

Group GENDER field of EMPLOYEE table in MySQL1 database

9

=A1.group(GENDER;avg(SALARY):avg)

Group GENDER field of EMPLOYEE table in demo database

10

=A9.join(GENDER,A8:GENDER,group)

11

=A10.news@1(group;EID,NAME,GENDER,age(~.BIRTHDAY):age,salary,avg:AvgSalary)

Compute each GENDER value for each group, generate an empty table for the group where GENDER is M and then concatenate result records into a new table sequence

Related functions:

cs.new()

ch.news()

Description:

Get new values for the fields of records in a channel and update them into the channel.

Syntax:

ch.news(X;xi:Fi,…)

Note:

With channel ch, the function computes values of fields in record sequence X and updates the new field values into the channel. Fi is the new field name that will be automatically identified if the parameter is omitted; xi represents the new field values. This is an attached computation.

Parameters:

ch

Channel

X

Record sequence

xi

Expression, whose results will be field values; the sign ~ used in the parameter references data from X instead of A. The sign # is used to represent a field with a sequence number

Fi

Field name in the given channel; will be automatically identified if the parameter is omitted

Return value:

The original channel with new fields

Example:

 

A

 

1

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

 

2

=demo.query("select * from EMPLOYEE")

 

3

=channel()

Create a channel

4

=A2.group(GENDER;~:group)

Group records by GENDER

5

=A1.groupx(GENDER;avg(SALARY):avg)

Group records by GENDER

6

=A5.join(GENDER,A4:GENDER,group)

 

7

=A3.news(group;EID,NAME,GENDER,#3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,avg:AvgSalary)

A6 is one of the groups; compute field values based on each group and concatenate the results into A3’s channel

8

=A3.fetch()

Fetch and store the existing data in the channel

9

=A6.push(A3)

Be ready to push data in A6’s cursor into A3’s channel, but the action needs to wait

10

=A6.fetch()

Data in A6’s cursor is pushed into the channel and operations are performed as the fetch() operation is performed over A6

11

=A3.result()

cs.news()

Description:

Compute new cursor field values to update them into the original cursor.

Syntax:

cs.news(X;xi:Fi,…)

Note:

The function computes field values based on cursor cs, updates the newly-computed fields into the original cursor where xi is field value of each record and Fi is the new field name, which will be automatically identified if Fi is omitted.

Parameters:

cs

Cursor

X

Record sequence

xi

An expression, whose values are uses as the new field values. It is treated as null if omitted; in that case, : Fi can’t be omitted. The sign # is used to represent a field with a sequence number

Fi

Filed name of the new cs; use the identifiers parsed from expression xi if it is omitted

Options:

@1  Left join, which creates an empty record when record sequence X is empty

Return value:

The original cursor with the new field values

Example:

 

A

 

1

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

 

2

=demo.query("select * from EMPLOYEE")

 

3

=A2.group(GENDER;~:group)

Perform group by GENDER

4

=A1.groupx(GENDER;avg(SALARY):avg)

Perform group by GENDER

5

=A4.join(GENDER,A3:GENDER,group)

 

6

=A5.news(group;EID,NAME,GENDER,#3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,avg:AvgSalary)

A4 is composed of data groups; based on each group field values are computed and a cursor is generated; and then cursors are concatenated into a new one.

7

=A6.fetch()

 

8

=MySQL1.query("select * from EMPLOYEE")

 

9

=A8.group(GENDER;~:group)

Group GENDER field

10

=A4.join(GENDER,A9:GENDER,group)

11

=A10.news@1(group;EID,NAME,GENDER,age(~.BIRTHDAY):age,salary,avg:AvgSalary)

Compute each GENDER value for each group, generate an empty table for the group where GENDER is M and then concatenate result records into a new table sequence

12

=A11.fetch()

Related functions:

A.news(X;xi:Fi,…)

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

Description:

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

Syntax:

T.news(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 A or cursor cs and concatenates the values to form a new table sequence. The table sequence and cursor should be ordered by the key.

It is used to join a table sequence or a cursor to a composite in a one-to-many relationship and return a result set containing all records in the composite table pointing to the table sequence or cursor’s primary key. While T.new() function performs a join in a one-to-one relationship.

 

Parameters:

T

A composite table

A/cs

A table sequence/cursor/composite table cursor

x

Field values

C

Column alias

wi

Filtering condition; multiple conditions are separated by comma(s) and they must be all met at the same time

Return value:

A table sequence/cursor

Example:

 

A

 

1

=file("score.ctx").create()

Open a composite table file

2

=demo.query("select * from students").keys(ID)

Return a table sequence with ID as the key

3

=A1.news(A2,STUDENTID,SUBJECT,SCORE;SUBJECT=="Math",SCORE>70)

Join A1’s composite table and A2’s table sequence where the filteing condition is SUBJECT=Math & SCORE>70 and return eligible records in the composite table