switch()

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 A_i’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 F_i field. If x is #, locate the records directly through the sequence numbers.

@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

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)

ch

Channel

F_i

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

F_i, A_i

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.

F_i, A_i: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 F_i field.

@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

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

A cursor/A multicursor

F_i

A field of a cursor

A_i

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

@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

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

cs

A cursor/multicursor/cluster cursor

F_i

A field in the cursor

A_i

A cluster memory table

x

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

@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

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

An in-memory table

Fi

A field of T

Ai

An in-memory table or a table squence

x

The (logical) primary key of Ai; if a specific primary is set for Ai, the parameter can be omitted

@i

Delete a record from T if its Fi value does not have a match in Ai

@d

Opposite to @i option, it enables to retain only the non-matching records in T; and in this case, Fi won’t be recorded as null

@1

Generate a record of same structure as Ai if the Fi value of a record of T does not have a match in Ai; the primary key of the new record will be Fi

A

1

=connect("demo").cursor("select EID,NAME,GENDER,DEPT,SALARY from employee where EID<11")

2

=A1.memory()

Return an in-memory table

3

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

Return a table sequence with DEPT being the key

4

=A2.switch(DEPT,A3)

Replace values of DEPT field in A2’s in-memory table with the referencing field values in A3, and display non-matching values as nulls

5

=connect("demo").cursor("select EID,NAME,GENDER,DEPT,SALARY from employee where EID<11").memory()

Return an in-memory table as A2 does

6

=A5.switch@i(DEPT,A3)

Replace values of DEPT field in A5’s in-memory table with the referencing field values in A3, and with @i option, delete the non-matching records

7

=connect("demo").cursor("select EID,NAME,GENDER,DEPT,SALARY from employee where EID<11").memory()

Return an in-memory table as A2 does

8

=A7.switch@d(DEPT,A3)

Replace values of DEPT field in A7’s in-memory table with the referencing field values in A3, and with @d option, keep only the non-matching records

9

=connect("demo").cursor("select EID,NAME,GENDER,DEPT,SALARY from employee where EID<11").memory()

Return an in-memory table as A2 does

10

=A9.switch@1(DEPT,A3)

Replace values of DEPT field in A9’s in-memory table with the referencing field values in A3, and with @1 option, generate a record of A3’s structure when a record does not match any record of A3

A

1

=connect("demo").cursor("select EID,NAME,GENDER,DEPT,SALARY from employee where EID>490")

2

=A1.memory()

Return an in-memory table

3

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

4

=A3.memory()

Return an in-memory table

5

=A2.switch(DEPT,A4:DEPT)

Replace values of DEPT field in A2’s in-memory table with the referencing field values in A4