Structured Query Language, abbreviated as SQL, is usually used to query, store and retrieve data in databases. In esProc, users can return the query result using SQL commands directly, or process data step by step with more flexible and convenient methods and get the same result as that of SQL, whose computation is complicated.
1. esProc syntax and SQL syntax for common computations
1.1. Select the specified fields
| A | B | |
| 1 | =demo.query(“select * from STATES”) | |
| 2 | =demo.query(“select NAME as STATE,CAPITAL from STATES”) | =B1.new(NAME:STATE,CAPITAL) |
With T.new() function, specified fields can be selected from the pre-existing table sequence T to create another table sequence. Actually, an SQL statement is usually used directly to select the specified fields if other fields of the data table aren’t needed. Results in A2 and B2 are the same:
1.2 Filter records with “where”
| A | B | |
| 1 | =demo.query(“select * from STATES”) | |
| 2 | =demo.query(“select * from STATES where POPULATION>15000000”) | =B1.select(POPULATION>15000000) |
With T.select() function, specified records can be selected from table sequence T to create a record sequence. In both A2 and B2, data of states where population is greater than 15,000,000 are selected, and the results are the same:
1.3 Remove duplicates with “distinct”
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select distinct STATEID from CITIES”) | =B1.id(STATEID) |
With T.id() function, the result, in which duplicates have been removed, can be obtained from table sequence T. In both A2 and B2, the serial numbers of states, in which cities listed in table CITIES are located, can be viewed. The results are the same:
1.4 Sort with “order by”
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select * from CITIES order by NAME”) | =B1.sort(NAME) |
| 3 | =demo.query(“select * from CITIES order by NAME desc”) | =B1.sort(NAME:-1) |
With T.sort() function, the records in table sequence T can be sorted and a new record sequence will be returned. In A2 and B2, data are sorted by city names in ascending order:
In A3 and B3, data are sorted by city names in descending order:
1.5 Group and summarize with “group by”
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select STATEID, count(*) as CITYCOUNT from CITIES group by STATEID”) | =B1.groups(STATEID; count(~):CITYCOUNT) |
The SQL statement in A2 groups the cities and counts the cities which each state has in the table CITIES. The result is as follows:
In B2, groups function is used in esProc to group and summarize the data of the table sequence. The result is as follows:
The summarizing results obtained by using both methods are the same. The only difference is that data are automatically sorted in esProc according to the grouping criteria during the process of grouping and summarizing.
1.6 Foreign keys
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select * from STATES where STATEID<10”) | |
| 3 | >B1.switch(STATEID, B2) | |
| 4 | =demo.query(“select CITIES.NAME as CITY, S.ABBR as STATE from CITIES left join (select * from STATES where STATEID<10) as S on CITIES.STATEID = S.STATEID”) | =B1.new(NAME:CITY, STATEID.ABBR:STATE) |
In databases, the relationship between two tables is usually represented by the foreign key. For example, relate the table of cities information –CITIES – to table of states information –STATES – by field STATEID of the former table. In order to show the computational method of the relating, only the data of states whose serial numbers are less than 10 are selected. Since a statement is needed to get the result when SQL is used to perform a task, methods like nested SQL, whose statements are complicated and unreadable though, are required. While in esProc, a step-by-step method is adopted: fetch data with B1 and B2; use switch function in B3 to connect data of the two tables. As shown below, the table sequence is changed in B1:
It can be seen that the data of field STATEID are set to correspond to the state records. Seen from the computation in B4, the value of fields of record type can be called conveniently, as the abbreviations of states are obtained using STATEID.ABBR. Then, the result in B4 is the same as that in A4:
1.7 Join multiple tables together with “join”
| A | B | |
| 1 | =demo.query(“select * from CITIES where STATEID>4”) | |
| 2 | =demo.query(“select * from STATES where STATEID<10”) | |
| 3 | =demo.query(“select C.NAME as CITY, S.ABBR as STATE from (select * from CITIES where STATEID>4) as C, (select * from STATES where STATEID<10) as S where C.STATEID = S.STATEID”) | =(join(B1:C,STATEID;B2:S,STATEID)).new(C.NAME:CITY, S.ABBR:STATE) |
| 4 | =demo.query(“select C.NAME as CITY, S.ABBR as STATE from (select * from CITIES where STATEID>4) as C inner join (select * from STATES where STATEID<10) as S on C.STATEID = S.STATEID”) | |
| 5 | =demo.query(“select C.NAME as CITY, S.ABBR as STATE from (select * from CITIES where STATEID>4) as C left join (select * from STATES where STATEID<10) as S on C.STATEID = S.STATEID”) | =(join@1(B1:C,STATEID;B2:S,STATEID)).new(C.NAME:CITY, S.ABBR:STATE) |
| 6 | =demo.query(“select C.NAME as CITY, S.ABBR as STATE from (select * from CITIES where STATEID>4) as C full join (select * from STATES where STATEID<10) as S on C.STATEID = S.STATEID”) | =(join@f(B1:C,STATEID;B2:S,STATEID)).new(C.NAME:CITY, S.ABBR:STATE) |
| 7 | =demo.query(“select C.NAME as CITY, S.ABBR as STATE from (select * from CITIES where STATEID>4) as C right join (select * from STATES where STATEID<10) as S on C.STATEID = S.STATEID”) | =(join@1(B2:S,STATEID;B1:C,STATEID)).new(C.NAME:CITY, S.ABBR:STATE) |
The SQL statements used in A3 and A4 are inner join, but they have different syntax. When the data of two tables are being joined, only the data that have inter-table relations with each other will be fetched. The returned result in A3, A4 and B3 are all the same. Note that the order of records in the results returned by SQL and esProc respectively may be not the same:
The SQL statement in A5 is a left join. When the data of the two tables are being joined, besides the data that have inter-table relations with each other, all the unrelated data in the first table will also be fetched. This means, in the result, field STATE may be null and field CITY won’t be null. This case, in fact, is the foreign key relationship. To join tables together in esProc, both switch and join@1() function, in which the 1 in @1 option is a digit, can be used. Letter l won’t appear in esProc options for fear of confusion. The returned results in A5 and B5 are the same. As the above, the order of records in the results returned by SQL and esProc respectively may be not the same:
The SQL statement in A6 is a full join. When the data of the two tables are being joined, all data of both tables after relating will be fetched, meaning both of the two fields: STATE and CITY, in the result may be null. The operation can be performed with join@f() function in esProc. The returned results in A6 and B6 are the same but the order of records in them may be different:
The SQL statement in A7 is a right join. When the data of the two tables are being joined, all the unrelated data in the second table, besides the data that have inter-table relations with each other, will be fetched. This means, in the result, the field CITY may be null but field STATE won’t be null. Without a similar usage, esProc needs to switch the positions of the parameters in join@1(), the left join function, to complete the same operation. The returned results in A7 and B7 are the same though the order of records in them may be different:
1.8 Union the results with “union”
| A | B | |
| 1 | =demo.query(“select * from CITIES where POPULATION>2000000”) | |
| 2 | =demo.query(“select * from CITIES where STATEID<6”) | |
| 3 | =demo.query(“(select * from CITIES where POPULATION>2000000) union all (select * from CITIES where STATEID<6)”) | =B1|B2 |
| 4 | =demo.query(“(select * from CITIES where STATEID>4) union (select * from CITIES where STATEID<6)”) | =[B1,B2].merge@u(CID) |
union all or union can be used in the SQL statements to union the data from two result sets. But in esProc, two sequences can be concatenated to merge the data from two result sets completely. The results in A3 and B3 are the same:
merge@u() function can be used in esProc to remove the duplicates in the result. The results in A4 and B4 are the same:
Only the first of the two duplicate records of Los Angeles in the previous result is kept.
2. esProc functions and SQL functions
We can use various functions in the SQL statements. But different databases require different syntax. For example, TODAY() is the standard SQL function to get the current date, but SYSDATE should be used in Oracle and GETDATE() is required in the SQL Server. This is so complicated. However, the trouble brought in by different syntax can be avoided if the data can first be fetched in esProc and then be processed as required.
2.1 Logical judgment
The operators for logical judgment used in esProc are similar to those used in high-level programming languages, like Java. But they are different from the SQL operators of the same use:
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select * from CITIES where STATEID=5”) | =B1.select(STATEID==5) |
| 3 | =demo.query(“select * from CITIES where STATEID=5 OR STATEID=13”) | =B1.select(STATEID==5 || STATEID==13) |
| 4 | =demo.query(“select * from CITIES where STATEID=5 AND POPULATION>1000000”) | =B1.select(STATEID==5 && POPULATION>1000000) |
In SQL, “=”is used to judge if two things are equal, “OR” to represent the “OR operation” and “AND” to represent the “AND operation”. While in esProc, the double equals “==” is used to judge if two things are equal,“||”to represent the “OR operation” and “&&” to represent the “AND operation”.
In A2 and B2, the records of cities, in which the value of field STATEID is 5, are selected:
In A3 and B3, the records of cities, in which the value of field STATEID is 5 or 13, are selected:
In A4 and B4, the records of cities, in which the value of field STATEID is 5 and the value of POPULATION is greater than 1,000,000, are selected:
2.2 String functions
The usage of string functions in esProc is similar to that of standard functions in SQL, for example:
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select * from CITIES where left(NAME,1)=’F'”) | =B1.select(left(NAME,1)==”F”) |
| 3 | =demo.query(“select * from CITIES where length(NAME)=4”) | =B1.select(len(NAME)==4) |
| 4 | =demo.query(“select * from CITIES where NAME like ‘C%e'”) | =B1.select(like(NAME,”C*e”)) |
| 5 | =demo.query(“select CONCAT(STATEID,NAME) as VALUE from CITIES”) | =B1.new(string(STATEID)+NAME:VALUE) |
The usages of left function in A2 and B2 are completely the same. But to get cities whose initials are F, the string is single-quoted in the SQL command while it is double-quoted in the esProc expression:
The string functions that have similar syntax include right/mid/replace and so on. In addition, the usages of mathematical functions like abs/ceil/round/floor/sin/cos/tan/power/sqrt in SQL and esProc are basically the same.
The names of some corresponding functions are different in SQL and in esProc. For example, the function for returning the length of a certain string is length in SQL while its counterpart in esProc is len. In A3 and B3, the cities whose names are composed of four letters are selected:
And the usages of some functions are also different in SQL and in esProc. For example, keyword like is used in SQL to judge if a string is of the specified format while like function is used in esProc to do the same thing. The cities starting with a C and ending with an e are listed in A4 and B4:
To concatenate strings, concat function is used in SQL, while a “+” can be used directly in esProc though the digits should be converted into the string first. The results in A5 and B5 are the same:
2.3 Date/time functions
| A | B | |
| 1 | =demo.query(“select * from EMPLOYEE”) | |
| 2 | =B1.new(EID,NAME+” “+ SURNAME:FULLNAME,BIRTHDAY) | |
| 3 | =demo.query(“select EID,concat(NAME,’ ‘,SURNAME) as FULLNAME,BIRTHDAY from EMPLOYEE where year(BIRTHDAY)=1980”) | =B2.select(year(BIRTHDAY)==1980) |
| 4 | =B3.derive(age(BIRTHDAY):AGE) |
All the common date/time functions in SQL can find their counterparts in esProc and their usages are similar. For example, the year function for getting the year value in A3 and B3. Similar functions include month/day/hour/minute/second and etc. The data of employees who were born in 1980 are fetched in both A3 and B3:
esProc boasts some easy-to-use functions SQL hasn’t. For example, age function is used in B4 to compute the ages of employees in the result. But the same computation is difficult to perform in SQL. The result in B4 is as follows:
2.4 Summary functions
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =demo.query(“select STATEID, count(*) as CITYCOUNT, sum(POPULATION) as POPULATION, avg(POPULATION) as AVGPOP from CITIES group by STATEID”) | =B1.groups(STATEID; count(~):CITYCOUNT, sum(POPULATION):POPULATION, avg(POPULATION):AVGPOP) |
The usages of common summary functions, such as count/sum/avg/max/min, are the same in esProc and in SQL. The results in A2 and B2 are the same, but the data in B2 have been sorted by grouping conditions:
