Using SQL in esProc (I)

In esProc, we can use not only the SQL to retrieve data from databases, but also the preliminary database query results to perform further analyses and operations to solve some complicated problems which are difficult to deal with only with SQL.

1.Database connection and disconnection

When using SQL to access databases, it should first connect to the designated database. Usually, there are two ways to connect to the database: direct connection in the data source manager or calling functions to connect in cellsets.
esProc_sql_1
esProc_sql_2
In practice, the database accessed in the data source manager can be called by directly using data source name and are valid while the connection is on; for database accessed through functions, the connection objects it created will be stored as cell values,and it can be called by using cell names and are valid before the called connection objects close.
esProc_sql_3
Similarly, there are two ways for database disconnection as well. Except for calling db.close() function shown in the above example,another way is to close the selected data source in data source manager:
esProc_sql_4
 

2. Use of simple SQL

Using db.query() function can execute SQL orders in designated database.SQL statements may contain various query clauses and database functions.
esProc_sql_5
A2 executes query on states whose abbreviations begin with N, and sort them by population in descending order. Results are as follows:
esProc_sql_6
SQL statements can also use other data in cellsets as parameters:
esProc_sql_7
A4 executes query on states whose abbreviations fall into designated sequences and sort them by area in ascending order. Results are as follows:
esProc_sql_8

3.SQL that returns no results

If SQL that returns no results is used to access a database, like the use of SQL statements:create, update, delete and so on, then db.execute() function is needed in execution. Meanwhile, since it is not necessary to assign value to cells, expressions begin with “>” instead of “=”. For example:
esProc_sql_9
After statements in A2 modifies records in database table STATES, query results of A3 are as follows:

esProc_sql_10
SQL that returns no results can use parameters, too:
esProc_sql_11
Statements in A4 restore modified records in database STATES to their original values, and query results of A5 are as follows:
esProc_sql_12
 

4. Use of query results of SQL

Query results of SQL can be used in esProc to perform operations, like filtering, sorting and combination, etc.in order to increase query efficiency or deal with some complicated problems.

In the following examples, executions are performed by connecting to data source demo in data source manager and based on query results of cell A1:
esProc_sql_13
esProc_sql_14
For example, filtering data and searching data of states of designated abbreviations:
esProc_sql_15
Also, aggregation computations can be performed on data. For example, count up the number of states whose abbreviations begin with C:
esProc_sql_16
More significantly, we can group data in databases according to certain requirements, for example, groupaccording to initials of abbreviations:

esProc_sql_17 esProc_sql_18

A2 executes grouping according to the initial of each state’s abbreviation. Double-click and see details of each group’s data.

It is thus clear that, different from the SQL grouping method that doesn’t provide real grouping and summarizing function, grouping with esProc is the real one, on which further computations can be performed. For example, select groups that contain three or more states, compute the total number and population of the states in these groups:
esProc_sql_19
The final results of A4 are:
esProc_sql_20

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Big Data, Data Analytics, Program Language, Reporting tool and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s