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 sequence 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:
ch.switch(Fi,Ai:x;… )
Description:
Attach a referencing field-based switch operation to a channel and return the original channel.
Syntax:
ch.switch(Fi,Ai:x;…)
Note:
The function attaches a computation to channel ch, which will switch values of field Fi to the referenced records of Ai, and return the original channel ch.
x is Ai’s primary key or logical primary key. The matching condition for the switch is Fi=x. By default, display Fi value as null when no match can be found in Ai for Fi.
This is an attached computation.
Parameter:
|
ch |
A channel |
|
Fi |
A field in channel ch |
|
Ai |
A table sequence/record sequence |
|
x |
Ai’s primary key or logical primary key; if x is already set as the primary key of Ai, parameter x can be omitted |
Option:
|
@i |
If no value corresponding to Fi 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 Fi 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; note that the option uses number 1 |
Return value:
Channel
Example:
x is Ai’s primary key:
|
|
A |
|
|
1 |
=demo.cursor("SELECT top 10 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 using DEPT as the key:
|
|
3 |
=channel() |
Create a channel. |
|
4 |
=A3.switch(DEPT,A2) |
Attach a computation to channel A3, which will switch DEPT field values to A2’s records pointed by the referencing field, and return result to channel A3; as A2’s key is DEPT, parameter x can be omitted. |
|
5 |
=A4.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.push(A3) |
Be ready to push cursor A1’s data to channel A3, but the action needs to wait. |
|
7 |
=A1.skip() |
Fetch data from cursor A1 while pushing data to channel to execute the attached computation and keep the result. |
|
8 |
=A3.result() |
Get channel A3’s result:
|
x is Ai’s primary key or logical primary key:
|
|
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 |
=channel(A1) |
Be ready to push cursor A1’s data to channel A3, but the action needs to wait. |
|
4 |
=A3.switch(DEPT,A2:DEPT) |
Attach a computation to channel A3, which will match its DEPT field with the corresponding DEPT field value in A2 – display a result record as null if no match can be found, and return the result to channel A3. |
|
5 |
=A4.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.skip() |
Fetch data from cursor A1 while pushing data to channel to execute the attached computation and keep the result. |
|
7 |
=A3.result() |
Get channel A3’s result:
|
Use @i option to directly delete records in the channel that do not have matches:
|
|
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 |
=channel(A1) |
Be ready to push cursor A1’s data to channel A3, but the action needs to wait. |
|
4 |
=A3.switch@i(DEPT,A2:DEPT) |
Attach a computation to channel A3, which will match its DEPT field with the corresponding DEPT field value in A2 – use @i option to delete records that cannot find matches, and return the result to channel A3. |
|
5 |
=A4.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.skip() |
Fetch data from cursor A1 while pushing data to channel to execute the attached computation and keep the result. |
|
7 |
=A3.result() |
Get channel A3’s result:
|
Use @d option to retain 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 |
=channel(A1) |
Be ready to push cursor A1’s data to channel A3, but the action needs to wait. |
|
4 |
=A3.switch@i(DEPT,A2:DEPT) |
Attach a computation to channel A3, which will match its DEPT field with the corresponding DEPT field value in A2 – use @d option to retain only the non-matching records, and return the result to channel A3. |
|
5 |
=A4.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.skip() |
Fetch data from cursor A1 while pushing data to channel to execute the attached computation and keep the result. |
|
7 |
=A3.result() |
Get channel A3’s result:
|
Use @1 option;
|
|
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 |
=channel(A1) |
Be ready to push cursor A1’s data to channel A3, but the action needs to wait. |
|
4 |
=A3.switch@i(DEPT,A2:DEPT) |
Attach a computation to channel A3, which will match its DEPT field with the corresponding DEPT field value in A2 – use @1 option to generate a record of A2’s structure when no match can be found, and return the result to channel A3. |
|
5 |
=A4.fetch() |
Execute the result set function in channel A2 and keep the current data in channel. |
|
6 |
=A1.skip() |
Fetch data from cursor A1 while pushing data to channel to execute the attached computation and keep the result. |
|
7 |
=A3.result() |
Get channel A3’s result:
|
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. |
|
@i |
If no value corresponding to Fi 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 Fi 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:
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:
T.switch( Fi,Ai:x;… )
Define a field value switching operation on a pseudo table and return a new pseudo table.
Syntax:
T.switch(Fi,Ai:x;…)
Note:
The function defines a computation on pseudo table T, which will replace values of Fi field in pseudo table T with corresponding values in table sequence/record sequence Ai by matching the Fi value with parameter x, which is the primary key or logical primary key of Ai and returns a new pseudo table. 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 |
B |
|
|
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) |
=A4.import() |
Execute expression in A4 to define a computation on A2’s pseudo table, which will convert its DEPT field values into corresponding referencing field values in table sequence A3, and retrun a new pseudo table. Execute expression in B4: import data from A4’s pseudo table while executing the computation deinfed in A4 on A2’s pseudo table, and return the following table:
|
|
5 |
=A2.switch@i(DEPT,A3) |
=A5.import() |
Use @i option to delete pseudo table records that can’t be matched; execute B5 and return the following table:
|
|
6 |
=A2.switch@d(DEPT,A3) |
=A6.cursor().fetch() |
Use @d option to retain only the non-matching records in the pseudo table; execute B6 and return the following table:
|
|
7 |
=A7.cursor().fetch() |
Use @1 option to generate a record having same structure as table sequence A3 when a DEPT value of the pseudo table does not have a match in A3 and using DEPT as the primary key; execute B7 and return the following table:
|
