merge()

Read(795) Label: merge,

Here’s how to use merge() function.

A .merge()

Description:

Merge multiple table sequences/record sequences.

Syntax:

A.merge (xi,…)

Note:

The function merges multiple table sequences/record sequences A(i)s (which can be represented as A(i)|…) according to all fields when parameter xi is omitted and when primary keys are not set for A, a sequence of table sequences/record sequences. For records having same xi, first retrieve those in A(i) and then those in A(i+1).

Parameter:

A

Multiple table sequences/record sequences of the same structure

xi

A field of A(i); if performing merge by multiple fields, use the comma to separate them, for example, x1,x2...

Option:

@u

Remove the duplicates from the table sequence/record sequence generated from unioning members of A(i)s in certain order; records with same xi have same corresponding members of A(i)

@i

Return a table sequence/record sequence composed of the common members of A(i)s

@d

Generate a new table sequence/record sequence by removing members of A(2)&…A(n) from A(1).

@o

Do not assume that A(i) is already sorted by [xi,…]

@0

Put records with null values at the end

@x

Remove common members of A(i) and union the other members to generate a new table sequence/sequence

Return value:

Table sequence/record sequence

Example:

 

A

 

1

=demo.query("select EID,NAME,GENDER,SALARY from EMPLOYEE where EID<6")

 

EID field is ordered

2

=demo.query("select EID,NAME,GENDER,SALARY from EMPLOYEE where EID>3")

EID field is ordered

3

=[A1,A2].merge(EID)

 

Merge A1 and A2 in order by EID field

4

=[A1,A2].merge@u(EID)

Merge A1 and A2 in order by EID field and remove duplicates from the result

5

=[A1,A2].merge@i(EID)

Merge A1 and A2 in order by EID field but only keep records that have duplicates

6

=[A1,A2].merge@d(EID)

Delete A2’s members from A1

7

=[A1,A2].merge@o(SALARY)

8

=[A1,A2].merge@x(EID)

Merge A1 and A2 to get a new sequence by removing their common members

9

=demo.query("select * from EMPLOYEE where GENDER = 'M'").keys(EID)

10

=demo.query("select * from EMPLOYEE where GENDER = 'F'").keys(EID)

11

=[ A9,A10].merge()

With xi omitted, this table sequence is the result of merge by the order of the primary keys

12

=demo.query("select * from EMPLOYEE where GENDER = 'M' and EID<15")

 

13

=demo.query("select * from EMPLOYEE where GENDER = 'M' and EID>=15")

 

14

=[A12,A13].merge(EID,GENDER)

Merge by the EID field and GENDER field

15

=A1.run(EID=null)

 

16

=[A2,A15].merge(EID)

17

=[A2,A15].merge@0(EID)

Records with null values are put in the end

Related functions:

  CS.merge()

 

CS.merge()

Description:

Merge data of the member cursors of a cursor/composite table cursor sequence.

Syntax:

CS.merge(xi,…)

Note:

CS is a sequence of cursors ordered by [xi,…], and from each cursor a sequence of records can be output. The function merges the records of these cursors by the expression xi. Members of the cursor sequence must be of the same structure. If members are multicursors, they must have the same number of parallel cursors and be segmented synchronically.

When parameter CS is a sequence of composite table cursors or one of multicursors, the merge will be performed automatically by the dimension and the fields before it.

Parameter:

CS

A sequence of cursors/composite table cursors.

xi

An expression. If performing merge by multiple fields, use comma to separate them, for example, x1,x2...

Option:

@u

Union operation. Remove the duplicate records from the resulting cursor obtained by unioning the cursor members of CS in certain order. By default, the duplicate records are included

@i

Intersection operation. Return a cursor composed of common members of members of CS, the sequence of cursors.

@d

Difference operation. Create a new cursor by removing members of CS2&CSn from CS1

@0

Put records with null values at the end

@x

Only merge distinct records in member cursors

Return value:

A cursor

Example:

 

A

B

C

 

 

1

=demo.cursor("select * from STOCKRECORDS")

 

 

Retrieve data and return a cursor

 

2

For

 

 

 

 

3

 

=A1.fetch(500)

 

Fetch data from the cursor

4

 

if B3==null

break

 

5

 

else

 

 

6

 

 

=B3.sort(STOCKID)

Sort by STOCKID

7

 

 

=file("D:\\"+"a"+string(A2)+".txt").export@t(C6)

Store data retrieved each time in a file.

8

 

 

=B1=B1|file("D:\\"+"a"+string(A2)+".txt")

File object sequence

9

for B1

 

 

 

10

 

=A9.cursor@t()

 

 

11

 

=C1=C1|B10

 

File cursor sequence

12

=C1.merge(STOCKID)

 

 

Merge members of the sequence in order by STOCKID.

13

=A12.fetch()

 

 

Get records from the merged cursor

14

=directory@p("D://*.txt")

 

 

Below are several txt files:

15

for A14

 

 

 

16

 

=file(A15).cursor@t()

=B14=B14|B16

 

17

=B14.merge@i(STOCKID)

=A17.fetch()

 

 Return a cursor composed of their common members, with STOCKID already ordered.

18

=directory@p("D://*.txt")

 

 

The txt files are

the same as the above

19

for A18

 

 

 

20

 

=file(A19).cursor@t()

=B18=B18|B20

 

21

=B18.merge@u(STOCKID)

=A21.fetch()

 

 

Return a new cursor in which

duplicate records have been removed. STOCKID is already ordered.

 

22

=directory@p("D://*

txt")

 

 

The txt files are

the same as above

23

for A22

 

 

 

24

 

=file(A23).cursor@t()

=B22=B22|

24

 

25

=B22.merge@d(STOCKID)

=A25.fetch()

 

 

A new cursor created by removing from the first cursor the members of the other cursors; STOCKID is already ordered.

26

=directory@p("D://*.txt")

 

 

 

27

for A26

 

 

 

28

 

=file(A27).cursor@t()

=B26=B26|B28

 

29

=B26.merge@x(STOCKID)

=A29.fetch()

 

Get dinstinct recors from the cursors to create a new cursor ordered by STOCKID

Related function:

A.merge()