switch()

Read(1685) Label: switch,

Here’s how to use switch() function.

P .switch( F i , A i : x ;…)

Description:

Switch the values of the reference field between the corresponding key values and the referenced records, or, sometimes switch between them in a reverse direction.

Syntax:

P.switch(Fi, Ai:x;…)

Note:

The function enables Fi to switch between the corresponding key values, which refers to the primary key value of the referenced record in Ai, and the referenced records of Ai; , and returns a table sequence.

Take the Attendance table as an example. The employeeID field may store either the corresponding primary key values of employeeID or the referenced records in Employee table. So, the switch() function can be used to switch between the two objects.

Use the index table of the foreign key if any and create one if unavailable.

Parameter:

P

A table sequence/record sequence

Fi

The key of P; when Ai:x;… is omitted, Fi stores the records of the referenced table Ai; they can be replaced by Ai’s primary key values through the function

Fi, Ai

When Ai is available, Fi is the reference field of P, which stores corresponding primary key values; the function is used to switch them to the referenced records; the matching condition is that Fi is equal to the primary key value of Ai; use null to represent a missing record

Fi, Ai:x

When both parameter Ai and the primary key x are available, get the first record from Ai in which value x is equal to Fi and make it the value of Fi field; if x is #, locate the records directly through the ordinal numbers

Option: (Available for Fi, Ai and Fi, Ai:x)

@i

Delete the records where no value corresponding to F is found during the matching

@d

Perform the inverse operation of @i, which obtains the records that have no value corresponding to F; with the option do not populate F with nulls

@1

If the specified F field value of a specified record in sequence P doesn’t exist in sequence B, generate a record of the same structure as one in B with F field as the primary key

Return value:

Table sequence

Example:

 

A

 

 

1

=demo.query("select * from DEPARTMENT").keys(DEPT)

 

 

2

=demo.query("select * from EMPLOYEE where EID < 10")

 

 

3

=demo.query("select * from DEPARTMENT" ).cursor().memory().keys(DEPT)

Return an in-memory table

 

4

>A2.switch(DEPT,A1:DEPT)

 

5

>A2.switch(DEPT,A3:DEPT)

A3 is an in-memory table; the result is the same as above

 

6

>A2.switch(DEPT)

Switch records of A2 corresponding to "DEPT " to field values

 

7

>A2.switch@i(DEPT,A1)

Delete this record if no value corresponding to DEPT is found

 

8

>A2.switch@d(DEPT,A1)

Obtain records that have no values corresponding to DEPT

9

>A1.delete(6)

 

10

=A2.switch(DEPT,A2:DEPT)

11

=A2.switch@1(DEPT,A2:DEPT)

Related function:

cs.switch()

ch .switch( Fi,Ai:x;… )

Description:

Switch the values of a reference field in a channel between the corresponding primary key values and the referenced records, or, sometimes switch between them in a reverse direction.

Syntax:

ch.switch(Fi,Ai:x;…)

Note:

Based on a channel, the function enables Fi to switch between the corresponding primary key values and the referenced records of Ai; the key values refer to the primary key values of the referenced records in Ai. This is an attached computation.

Parameter:

ch

Channel

Fi

The reference field of A. When Ai:x;… is omitted, Fi stores the records of the referenced table Ai . They can be replaced by Ai’s primary key values through the function.

Fi, Ai

When Ai is available, Fi is the reference field of A, which stores corresponding primary key values. The function is used to switch them to the referenced records in Ai.The matching condition is that Fi is equal to the primary key value of Ai.

Fi, Ai:x

When both Ai and x are available, get the first record from Ai in which value x is equal to Fi and make it the value of Fi field.

Option:

@i

If no value corresponding to F is found, then remove this record.

@d

Perform the inverse operation of @i, which obtains the records that have no value corresponding to F.

@1

If the F field value of a record in channel ch doesn’t exist in Ai , then generate a record of the same structure as Ai with expression x being the primay key

Return value:

Channel

Example:

 

A

 

1

=demo.cursor("select EID,SURNAME,DEPT,STATE from  EMPLOYEE")

 

2

=demo.query("select STATEID,NAME from STATES").keys(STATEID)

3

=channel()

Create a channel

4

=A1.push(A3)

Push data in A1’s cursor into A3’s channel

5

=A3.switch(STATE,A2:NAME)

Get the first record in A2’s table where NAME field value is equal to STATE field value and use is as the referencing record to replace the corresponding STATE value in the channel; the key value after switching is the referencing field’s primary key, i.e. STATEID

6

=A5.fetch()

Attach ch.fetch() function that gets the final result set to A3’s channel to fetch and store the existing data in the channel

7

=A1.fetch()

Fetch data from A1’s cursor

8

=A3.result()

Convert values of "STATE" field in A1 to the corresponding records in A2

9

=demo.cursor("select EID,SURNAME,DEPT,STATE from  EMPLOYEE where EID<20")

Return a cursor

10

=demo.query("select STATEID,NAME from STATES where STATEID<15").keys(NAME)

Return a table sequence whose primary key is NAME

11

=channel(A9)

Create a cursor in the channel and be ready to push A9’s cursor records into the channel

12

=A11.switch(STATE,A10)

Use A10’s primary key with parameter x being absent; the post-switch key is NAME

13

=A12.fetch()

 

14

=A9.skip()

 

15

=A11.result()

16

=demo.cursor("select EID,SURNAME,DEPT,STATE from  EMPLOYEE where EID<20")

 

17

=demo.query("select STATEID,NAME from STATES where STATEID<15").keys(NAME)

Same as A10

18

=channel()

Create a channel

19

=A18.switch@i(STATE,A17)

With @i option, delete a record that can’t match any STATE value in A17’s table sequence

20

=A18.fetch()

 

21

=A16.push(A18)

Push data in A16’s cursor to the channel

22

=A18.result()

Get result set from the channel

23

=demo.cursor("select EID,SURNAME,DEPT,STATE from  EMPLOYEE where EID<20")

 

24

=demo.query("select STATEID,NAME from STATES where STATEID<15").keys(STATEID)

Same as A2

25

=channel()

Create a channel

26

=A25.switch@1(STATE,A24:NAME)

Find from A24’s table seqeunce the first record where the NAME (the key) value is the same as STATE (the key) value, use it as the reference record to replace the STATE value in the channel; the new key value after the replacement is the reference record’s primary key – STATEID. If there isn’t a matching STATEID, generate a record of the same structure as A24 and set primay key as NAME

27

=A25.fetch()

 

28

=A23.push(A25)

Push data in A23’s cursor into the channel

29

=A25.result()

Fetch data from the channel

cs .switch( F i , A i : x ;…)

Description:

Attach the action of switching specified field values to the referencing record field values to a cursor and return the original cursor.

Syntax:

cs.switch(Fi,Ai:x;…)

Note:

The function attaches a computation to cursor cs, which will replace values of Fi field in cursor cs with the corresponding records in Ai, and returns the original cursor cs. x is the primary key or logical primary key of Ai, and the matching condition is Fi =x. By default, an Fi field value is displayed as empty when no record in Ai can match it.

This is a delayed function.

Parameter:

cs

A cursor/A multicursor.

Fi

A field of a cursor.

Ai

A table sequence/A record sequence.

x

The primary key or logical primary key of Ai; the parameter can be omitted if primary key is already set for Ai.

Option:

@i

If no value corresponding to F is found, then remove this record.

@d

Perform the inverse operation of @i to obtain cursor records that have no matching value Ai for F field.

@1

If the F field value of a record in channel ch doesn’t exist in Ai, then generate a record of the same structure as Ai with expression x being the primary key. Here is number 1.

Return value:

Cursor

Example:

When x is the primary key of Ai:

 

A

 

1

=demo.cursor("SELECT top 5 EID,DEPT,NAME FROM EMPLOYEE")

Return a cursor whose data is as follows:

2

=demo.query("SELECT DEPT,MANAGER FROM DEPARTMENT").keys(DEPT)

Return a table sequence whose key is DEPT:

3

=A1.switch(DEPT,A2)

Attach a computation to cursor A1, which will replace DEPT values to A2’s records pointed by the referencing field in the cursor, and return cursor A1; as A2’s key is DEPT, parameter x can be omitted.

4

=A1.fetch()

Fetch data from cursor A1 where A3’s computation is executed:

 

When x isn’t the primary key or logical key of Ai:

 

A

 

1

=demo.cursor("SELECT top 10 EID,DEPT,NAME FROM EMPLOYEE")

Return a cursor whose data is as follows:

2

=demo.query("SELECT top 6 DEPT,MANAGER FROM DEPARTMENT")

Return a table sequence:

3

=A1.switch(DEPT,A2:DEPT)

Attach a computation to cursor A1, which will match cursor A1’s DEPT field with corresponding DEPT field in table sequence A2 and display the non-matching values as nulls, and return cursor A1.

4

=A1.fetch()

Fetch data from cursor A1 where A3’s computation is executed:

 

Use @i option to delete non-matching records:

 

A

 

1

=demo.cursor("SELECT top 10 EID,DEPT,NAME FROM EMPLOYEE")

Return a cursor whose data is as follows:

2

=demo.query("SELECT top 6 DEPT,MANAGER FROM DEPARTMENT")

Return a table sequence:

3

=A1.switch@i(DEPT,A2:DEPT)

Attach a computation to cursor A1, which will match cursor A1’s DEPT field with corresponding DEPT field in table sequence A2 and, with @i option, delete the non-matching records, and return cursor A1.

4

=A1.fetch()

Fetch data from cursor A1 where A3’s computation is executed:

 

Use @d option to keep the non-matching records only:

 

A

 

1

=demo.cursor("SELECT top 10 EID,DEPT,NAME FROM EMPLOYEE")

Return a cursor whose data is as follows:

2

=demo.query("SELECT top 6 DEPT,MANAGER FROM DEPARTMENT")

Return a table sequence:

3

=A1.switch@d(DEPT,A2:DEPT)

Attach a computation to cursor A1, which will match cursor A1’s DEPT field with corresponding DEPT field in table sequence A2 and, with @d option, retain the non-matching records only, and return cursor A1.

4

=A1.fetch()

Fetch data from cursor A1 where A3’s computation is executed:

 

When @1 option works:

 

A

 

1

=demo.cursor("SELECT top 10 EID,DEPT,NAME FROM EMPLOYEE")

Return a cursor whose data is as follows:

2

=demo.query("SELECT top 6 DEPT,MANAGER FROM DEPARTMENT")

Return a table sequence:

3

=A1.switch@1(DEPT,A2:DEPT)

Attach a computation to cursor A1, which will match cursor A1’s DEPT field with corresponding DEPT field in table sequence A2 and, with @1 option, generate a record of same structure as A2 when the current value does not match, and return cursor A1.

4

=A1.fetch()

Fetch data from cursor A1 where A3’s computation is executed:

 

Related function:

P.switch()

cs.switch(Fi,Ai:x;…)

Description:

Replace values of a specified field in a cluster cursor with the corresponding referencing records in another table.

Syntax:

cs.switch(Fi,Ai:x;…)

Note:

The function replaces a value of Fi field in cursor/multicursor/cluster cursor cs with the corresponding record in Ai by matching the Fi value with parameter x, which is the primary key or logical primary key of Ai. An Fi field value displays as empty when no record in Ai matches it.

Parameter:

cs

A cursor/multicursor/cluster cursor

Fi

A field in the cursor

Ai

A cluster in-memory table

x

The primary key or logical primary key of Ai; the parameter can be omitted if primary key is already set for Ai

Option:

@c

With a distributed cluster in-memory table, the operation won’t involve a cross-node reference but it assumes that the referenced records are local

Return value:

The original cursor

Example:

 

A

 

1

=demo.cursor("SELECT * FROM DEPARTMENT")

Return a cursor

2

[192.168.0.110:8281,192.168.18.143:8281]

 

3

=file("emp_1.ctx":[1,2], A2)

 

4

=A3.open()

Open the composite table

5

=A4.cursor()

Return a cluster cursor

6

=A5.groups@c(DEPT:dept;sum(SALARY):totalSalary)

Return a cluster in-memory table

7

=A1.switch@c(DEPT,A6:dept)

Replace values of DEPT field of the cursor with the referencing field values and return the switched cursor

8

=A7.fetch()

Fetch data from the switched cursor:

T.switch( Fi,Ai:x;… )

Description:

Replace values of a specified field in a pseudo table with the corresponding referencing values in another table.

Syntax:

T.switch(Fi,Ai:x;…)

Note:

The function replaces a value of Fi field in pseudo table T with the corresponding value in Ai by matching the Fi value with parameter x, which is the primary key or logical primary key of Ai. By default, an Fi field value displays as empty when no record in Ai matches it.

Parameter:

T

A pseudo table

Fi

A pseudo table field

Ai

A table sequence/record sequence

x

The primary key or logical key of Ai; the parameter can be omitted if primary key is already set for Ai

Option:

@i

If no value corresponding to F is found, remove the pseudo table record

@d

Perform the inverse operation of @i to obtain pseudo table records that have no matching value in Ai for F field

@1

If the F field value of a pseudo table record doesn’t exist in Ai , then generate a record of the same structure as Ai with expression x being the primary key

Return value:

Pseudo table

Example:

 

A

 

1

=create(file).record(["emp_news.ctx"])

Below is content of composite table emp_news.ctx:

2

=pseudo(A1)

Generate a pseudo table from the composite table

3

=demo.query("SELECT top 4 * FROM DEPARTMENT").keys(DEPT)

Return a table sequence and set DEPT field as its primary key

 

4

=A2.switch(DEPT,A3)

Convert the pseudo table’s DEPT field values into corresponding referencing field values in table sequence A3; below is content of the returned pseudo table:

5

=A2.switch@i(DEPT,A3)

Use @i option to delete pseudo table records that can’t be matched

6

=A2.switch@d(DEPT,A3)

Use @d option to retain only the non-matching pseudo table records

7

=A2.switch@1(DEPT,A3)

Use @1 option to generate a record having same structure as table sequence A3 and using DEPT as the primary key when a DEPT value of the pseudo table does not have a match in A3