switch()

Read(205) Label: switch,

Here’s how to use switch() function.

A .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:

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

Note:

The function enables Fi to switch between the corresponding primary key values and the referenced records of Ai; the key value refers to the primary key value of the referenced record in Ai.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.

Parameters:

A

A sequence.

Fi

The key 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. 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.

Options: (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 A 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:

The record sequence after going through a reference value switch.

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 a memory table

 

4

>A2.switch(DEPT,A1:DEPT)

 

5

>A2.switch(DEPT,A3:DEPT)

A3 is a 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,A9:DEPT)

11

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

Related functions:

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.

Parameters:

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.

Options:

@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.query("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 table sequence to the channel

22

=A18.result()

Get result set from the channel

23

=demo.query("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 table seqeunce into the channel

29

=A25.result()

Fetch data from the channel

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

Description:

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

Syntax:

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

Note:

The function replaces a value of Fi field in 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 there’s no record in Ai that can match it.

Parameters:

cs

A cursor/A multicursor

Fi

A field of a cursor

Ai

A table sequence/A record sequence

x

The primary key or logical of Ai; the parameter can be omitted if Ai has the primary key

Options:

@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:

The original cursor with switched field values

Example:

 

A

 

1

=demo.cursor("SELECT EMPLOYEE.EID,EMPLOYEE.DEPT,EMPLOYEE.NAME,

EMPLOYEE.SALARY FROM EMPLOYEE  where EID<11")

Return retrieved data as a cursor. Following is data in the cursor:

2

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

Return result as a table sequence where DEPT field is set as the key:

3

=A1.switch(DEPT,A2)

DEPT field is already set as the key of A2’s table sequence, so parameter x can be omitted; the operation transfers DEPT field values in A1 with primary key (the DEPT field) values of the corresponding records in A2

Click R&D value in the first row and the following record will be displayed:

4

=A3.fetch()

 

5

=demo.cursor("SELECT EMPLOYEE.EID,EMPLOYEE.DEPT,EMPLOYEE.NAME,EMPLOYEE.SALARY FROM EMPLOYEE  where EID<11")

Return retrieved data as a cursor. Following is data in the cursor:

6

=demo.query("SELECT * FROM DEPARTMENT where MANAGER<5")

Return result as a table sequence

7

=A5.switch(DEPT,A6:DEPT)

Match records in A5’s cursor with those in the table sequence by comparing the given field in the former and the latter’s specified key; for records that don’t have matchs in the table sequence, DEPT field values are left empty

8

=A7.fetch()

 

9

=demo.cursor("SELECT EMPLOYEE.EID,EMPLOYEE.DEPT,EMPLOYEE.NAME,EMPLOYEE.SALARY FROM EMPLOYEE  where EID<11")

Same as A5

10

=demo.query("SELECT * FROM DEPARTMENT where MANAGER<5")

Same as A6

11

=A9.switch@i(DEPT,A10:DEPT)

With @i option, any record that can’t find a match in the table sequence is deleted

12

=A11.fetch()

 

13

=demo.cursor("SELECT EMPLOYEE.EID,EMPLOYEE.DEPT,EMPLOYEE.NAME,EMPLOYEE.SALARY FROM EMPLOYEE  where EID<11")

Same as A5

14

=demo.query("SELECT * FROM DEPARTMENT where MANAGER<5")

Same as A6

15

=A13.switch@d(DEPT,A14:DEPT)

Contrary to @i option, @d option tells the function to return only the records that haven’t matches in the table sequence

16

=A15.fetch()

 

17

=demo.cursor("SELECT EMPLOYEE.EID,EMPLOYEE.DEPT,EMPLOYEE.NAME,EMPLOYEE.SALARY FROM EMPLOYEE  where EID<11")

Same as A5

18

=demo.query("SELECT * FROM DEPARTMENT where MANAGER<5")

Same as A6

19

=A17.switch@1(DEPT,A18:DEPT)

With @1 option, for a DEPT value in A17 that doesn’t exist in A18, the function generates a record of the same structure as A18; the primay key is set as DEPT

20

=A19.fetch()

Related functions:

A.switch()

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

Description:

Replace values of a specified field in a cluster cursor with the referencing field values of the corresponding record 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 there’s no record in Ai that can match it.

Parameters:

cs

A cursor/multicursor/cluster cursor

Fi

A field in the cursor

Ai

A cluster memory table

x

The primary key or logical of Ai; the parameter can be omitted if Ai has the primary key

Options:

@c

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

Return value:

The switched 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@0("emp_1.ctx", A2)

Get a distributed composite table file

4

=A3.create()

Open the composite table

5

=A4.cursor()

Return a cluster cursor

6

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

Return a cluster 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: