P.switch(F i ,A i :x;… )
Description:
Replace values of the sequence number key field with the corresponding referenced records, or, sometimes vice versa.
Syntax:
P.switch(Fi, Ai:x;…)
Note:
The function replaces values of foreign key field Fi in table sequence/record sequence P with the corresponding records in table sequence/record sequence Ai according to the matching condition that Fi value is equal to Ai’s x field value (Fi value becomes null if it does not have a matching record), , and returns a table sequence with switched Fi values.
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 foreign key of P. |
|
Ai |
A table sequence/record sequence Fi is. |
|
x |
An Ai table field referenced by Fi field. It is by default Ai’s primary key. When this parameter is #, directly use the sequence number to locate the corresponding record. If Ai has a sequence-number-based index, symbol # can be omitted. |
Option:
|
@i |
Delete Ai records that do not have a matching Fi field value. |
|
@d |
Perform the inverse operation of @i, which obtains records that have no value corresponding to Fi; with the option do not populate F with nulls. |
|
@1 |
If the Fi field value of a specified P record doesn’t exist in Ai, generate a record of the same structure as Ai in Fi while setting Fi as the primary key. |
Return value:
Table sequence
Example:
|
|
A |
|
|
1 |
=demo.query("select top 5 * from DEPARTMENT") |
Return a table sequence:
|
|
2 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE") |
Return a table sequence:
|
|
3 |
=A2.switch(DEPT,A1:DEPT) |
Table sequence A2’s foreign key DEPT has a correspondence relationship with table sequence A1’s DEPT field. Replace the foreign key value with the referenced records in A1, and enter null if no matching A1 record can be found.
|
When parameter x is absent:
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT").keys(DEPT) |
Return a table sequence that use DEPT as the primary key. |
|
2 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE") |
Return a table sequence: |
|
3 |
=A2.switch(DEPT,A1) |
As parameter x is absent, match table A2’s foreign key DEPT with table A1’s primary key and replace the foreign key values with their referenced records in A1:
|
Use the sequence number represented by parameter x to locate corresponding record when table Ai has a sequence number-based index:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT * FROM CITIES") |
Return a table sequence:
|
|
2 |
=connect("demo").query("SELECT * FROM STATECAPITAL") |
Return a table sequence: |
|
3 |
=A2.index@n() |
Create a sequence number-based index for table A2. |
|
4 |
=A1.switch(STATEID,A2) |
A1’s CITIES table is the fact table, A3’s STATECAPITAL is the dimension table; The switch() function performs table association by replacing values of foreign key field STATEID in CITIES table with the referenced records in STATECAPITAL table. The computation uses the index table created on the foreign key, and by doing so, the sequence number key can be omitted from expression x. Here the statement is equivalent to A1.switch(STATEID,A2:#). The statement returns the following result set:
|
When @i option works:
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT").keys(DEPT) |
Return a table sequence that uses DEPT as the primary key.
|
|
2 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE") |
Return a table sequence. |
|
3 |
=A2.switch@i(DEPT,A1) |
With @i option, delete the whole A2 record when it does not have a matching key value in A1:
|
When @d option works:
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT").keys(DEPT) |
Return a table sequence that uses DEPT as the primary key.
|
|
2 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE") |
Return a table sequence. |
|
3 |
=A2.switch@d(DEPT,A1) |
With @d option, only return A2 records where DEPT field does not have a matching value in A1’s foreign key field:
|
When @1 option works:
|
|
A |
|
|
1 |
=demo.query("select * from DEPARTMENT").keys(DEPT) |
Return a table sequence that uses DEPT as the primary key.
|
|
2 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE") |
Return a table sequence. |
|
3 |
=A2.switch(DEPT,A1) |
With @1 option, generate a record of same structure as table A1 in DEPT field; the record’s primary key value is DEPT:
|
Related function: