Data Search

Read(223) Label: data search,

This chapter lists code examples about general data search, including SELECT * FROM , WHERE , SELECT FROM, AS, SELECT…, ORDER BY/ASC/DESC, DISTINCT, FISRT/LAST/TOP/BOTTOM, UNION/UNION ALL/INTERSECT/MINUS, SELECT FROM (SELECT ), SELECT (SELECT FROM) FROM, and CURSOR/FETCH.

SELECT * FROM …

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

 

SELECT * FROM …

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

 

WHERE …

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(SALARY>5000)

 

 

WHERE …

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(SALARY>5000)

 

 

SELECT … FROM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.new(EID,NAME)

 

 

SELECT … FROM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.new(EID,NAME)

 

 

AS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.new(EID:EmployeeNo,NAME+" "+SURNAME: EmployeeName)

 

 

AS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.new(EID:EmployeeNo,NAME+" "+SURNAME: EmployeeName)

 

 

SELECT …

 

A

 

1

=[[1,"Beverage"]].new(~(1):TypeNumber,~(2):Name)

Create a table sequence containing only one record

2

=create(TypeNumber,TypeName).record( [1,"Beverage",2,"Grain"])

Create an empty table sequence, and fill data in it

 

SELECT …

 

A

 

1

=[[1,"Beverage"]].new(~(1):TypeNumber,~(2):Name)

Create a table sequence containing only one record

2

=create(TypeNumber,TypeName).record( [1,"Beverage",2,"Grain"])

Create an empty table sequence, and fill data in it

 

ORDER BY/ASC/DESC

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.sort(BIRTHDAY,-SALARY)

 

 

ORDER BY/ASC/DESC

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.sort(BIRTHDAY,-SALARY)

 

 

DISTINCT

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.id(CLIENT)

Get the distinct value

3

=A1.(CLIENT)

All values available

4

=A1.([CLIENT,SELLERID])

All available values in the fields

 

DISTINCT

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.id(CLIENT)

Get the distinct value

3

=A1.(CLIENT)

All values available

4

=A1.([CLIENT,SELLERID])

All available values in the fields

 

FISRT/LAST/TOP/BOTTOM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.m(1).NAME

FIRST

3

=A1.m(-1).NAME

LAST

4

=A1.m(to(3))

TOP 3

5

=A1.to(-3)

BOTTOM 3

 

FISRT/LAST/TOP/BOTTOM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.m(1).NAME

FIRST

3

=A1.m(-1).NAME

LAST

4

=A1.m(to(3))

TOP 3

5

=A1.to(-3)

BOTTOM 3

 

UNION/UNION ALL/INTERSECT/MINUS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(DEPT=="Sales" || DEPT=="R&D")

 

3

=A1.select(SALARY>5000)

 

4

=A2|A3

UNION ALL

5

=A2&A3

UNION

6

=A2^A3

INTERSECTION

7

=A2\A3

DIFFERENCE

 

UNION/UNION ALL/INTERSECT/MINUS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(DEPT=="Sales" || DEPT=="R&D")

 

3

=A1.select(SALARY>5000)

 

4

=A2|A3

UNION ALL

5

=A2&A3

UNION

6

=A2^A3

INTERSECTION

7

=A2\A3

DIFFERENCE

 

SELECT … FROM (SELECT … )

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(DEPT=="Sales")

Query

3

=A2.count()

Aggregate the result set

 

SELECT … FROM (SELECT … )

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(DEPT=="Sales")

Query

3

=A2.count()

Aggregate the result set

 

SELECT (SELECT … FROM) FROM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=demo.query("select * from FAMILY")

 

3

=A1.derive()

 

4

=A1.run(EID=A2.select(EID:A1.EID))

Calculate the subtable reference first

5

=A1.new(NAME,EID.count():NumberOfMembers)

 

6

=A3.new(NAME,A2.select(EID:A3.EID).count(): NumberOfMembers)

Use the direct-write method

 

SELECT (SELECT … FROM) FROM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=demo.query("select * from FAMILY")

 

3

=A1.derive()

 

4

=A1.run(EID=A2.select(EID:A1.EID))

Calculate the subtable reference first

5

=A1.new(NAME,EID.count():NumberOfMembers)

 

6

=A3.new(NAME,A2.select(EID:A3.EID).count(): NumberOfMembers)

Use the direct-write method

 

CURSOR/FETCH

 

A

B

C

 

1

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

 

 

 

2

for

 

 

 

3

 

=A1.fetch(100)

 

Fetch 100 records each time

4

 

if B3==null

break

 

5

 

 

 

 

CURSOR/FETCH

 

A

B

C

 

1

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

 

 

 

2

for

 

 

 

3

 

=A1.fetch(100)

 

Fetch 100 records each time

4

 

if B3==null

break

 

5