news()

Read(404) 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

 

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

Description:

Retrieve specified fields from a pseudo table by matching its records with key values another pseudo table, a table sequence, or a cursor, and join them onto the latter to generate a new pseudo table.

Syntax:

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

Note:

The function retrieves x,... fields from pseudo table T by matching its records with the key values of another pseudo table T’, table sequence A, or cursor cs, which is ordered by their key, and joins them onto the latter to generate a new pseudo table.

This function associates T’/A/cs with pseudo table T according to a one-to-many relationship – another T.news(T’/A/cs,x:C,…;wi,...) function, however, performs a join according to a one-to-one relationship, and returns a result set consisting of records of T that match the primary key values of T’/A/cs.

Parameters:

T

A pseudo table

T’A/cs

A pseudo table/table sequence/cursor

x

Field values

C

Column alias

wi

Filtering condition separated by commas and where the relationship between them is AND

Return value:

A pseudo table

Example:

 

A

 

1

=file("scores.ctx").open().pseudo()

Generate a pseudo table as follows:

2

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

Return a table sequence whose primary key is ID

3

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

Join A1’s pseudo table with A2’s table sequence according to the filtering condition that SUBJECT is Math and SCORE is greater than 70

4

=A3.import()

Generate a pseudo table as follows:

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

Description:

Get a computed field based on a records sequence and join it onto a pseudo table.

Syntax:

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

Note:

The function, according to pseudo table T, computes expression xi on a specific field in records sequence, assigns the results to the field and renames it Fi, which will be automatically identified when not specified, and joins Fi field to T.

Parameters:

T

A pseudo table

X

A record sequence

xi

An expression whose results will be assigned to a specific field; the tilde ~ used in the expression represents a record in X instead of A, and the number sign # denotes locating values by sequence number of the corresponding field

Fi

Name of fields added to T; will automatically identified when the parameter is absent

Options:

@1

Left join that creates a null record when no matching value is found in record sequence X

Return value:

A pseudo table

Example:

 

A

 

1

=file("emp6.ctx").open().pseudo()

Generate a pseudo table as follows:

2

=T("PERFORMANCE.ctx")

Return a table sequence

3

=A1.news(A2;#2,BONUS+1000:Bonus_new)

Compute the second column and BONUS column in A2’s table sequence and add the results as a new column to A1’s pseudo table. Bonus new is the name of the computed column according to BONUS+1000

4

=A3.import()

Return a pseudo table as follows: