$()sql;…

Read(312) Label: simple sql, query,

Description:

Query an external table using simple SQL.

Syntax:

$()sql; 

Note:

The function performs a query over an external data file. As a normal table sequence, the external file can be directly queried by using “file name.extension” as its name, which makes it the external table. The query syntax, which we call simple SQL, is similar to the SQL syntax of the database query.

Support external data files in the format of txt, csv, xlsx, xls, btx (segmented binary files). An external data table file has headers by default. Use a relative path or an absolute path to access an external data table file; the relative path is relative to esProc main directory.

Refer to db.query(sql) function to learn more about the syntax of simple SQL.

Parameters:

sql

A “simple SQL” statement, like select * from filename.txt

A parameter value in the simple SQL statement

 

 

 Note: the parentheses in the $()sql;… function can be omitted.

Return value:

   Table sequence

Example:

 

A

 

1

$()select * from Persons.txt

Retreive records from the external table Persons.txt and return result as a table sequence

2

$select * from D:/Orders.txt

Make the query via the absolute path and return result as a table sequence

3

$select * from Persons.csv where Id_P=? or Id_P>?;2,2

Get records where Id_P is equal to or greater than 2 from the external table

4

$select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P

Perform a query through a join

5

$select  distinct(Id_P)  from Orders.btx

Get records where Id_P value is unique from the external table