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.
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.select(SALARY>5000) |
|
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.new(EID,NAME) |
|
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.new(EID:EmployeeNo,NAME+" "+SURNAME: EmployeeName) |
|
|
|
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 |
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.sort(BIRTHDAY,-SALARY) |
|
|
|
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 |
|
|
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 |
|
|
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 |
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.select(DEPT=="Sales") |
Query |
|
3 |
=A2.count() |
Aggregate the result set |
|
|
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 |
|
|
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 |
|
… |
|
|