query()

Read(390) Label: query,

Here’s how to use query() function.

db.query()

Description:

Execute a SQL statement on a data source and return a table sequence composed of the query results.

Syntax:

db.query( sql {,args …})

 

db.query(A,sql{,args …})

Perform sql based on A and return a table sequence composed of query results. Here, args can be an expression evaluated based on each member of A. This type of syntax can work with @idx options.

db.query(call dfx(){,args …})

Connect to esProc-ODBC data source db to call the specified dfx file and return a result

Note:

The function performs the specified SQL query sql on the data source db and returns a table sequence composed of query results. db is the database connection object.

When there’s only one table following FROM in the SQL statement sql, the key of the result table sequence will be automatically set.

Parameters:

db

Database connection object.

sql

A SQL statement, like select * from table.

args

An argument value passed into the specified SQL command sql. It can be a constant or an expression. The comma is used to separate multiple arguments.

A

A sequence. sql is executed on each member of A. Generally args is computed against each member of A and the value is passed to sql for execution.

dfx

A cellset file; can be specified using a relative path, which is relative to the search path, or an absolute path

Options:

@1

Return only the first-found record satisfying the condition, which is a single value if only one field is involved or a sequence if multiple fields are involved.

@i

Return a sequence if the result set has only one column.

@d

Convert the numeric data type to the double data type, instead of the decimal data type.

@x

Close the database connection automatically when the statement finishes execution. Only valid with the database connection established through the connect function.

Return value:

A table sequence composed of results of executing sql.

Example:

 

A

 

1

=demo.query("select * from EMPLOYEE where DEPT=? ","Sales")

 

2

=demo.query@1("select * from EMPLOYEE where DEPT=? ","Sales")

[3,"Rachel","Johnson","F","New Mexico",1970-12-17,2010-12-01,"Sales",9000]; with @1 option, a sequence composed of field values of the first record is returned.

3

[1,2,3,4]

 

4

=demo.query(A3,"select * from EMPLOYEE where EID=?",~)

5

=demo.query@i(A3,"select SURNAME from EMPLOYEE where EID=?",~)

Return the result as a sequence

6

=demo.query@i("select DEPT from DEPARTMENT")

Return the result as a sequence

7

=mysql.query@d("select * from ta")

8

=connect("demo")

 

9

=A8.query@x("select DEPT from DEPARTMENT")

Close the database connection automatically when the execution is over.

10

=myproc.query("call hsql(?)",10)

hsql.dfx is in the search path. Below is the file:

myproc is the esProc-ODBC data source name; the parameter value is 10. Below is the result:

11

=myproc.query("call G://hsql(?)",10)

hsql.dfx is in the root directoy on drive G; same content and same result as shown in A10

Related functions:

db.execute()

db.proc()

db.query( sql )

Description:

Query an external table using simple SQL and return the query result.

Syntax:

db.query( sql {,args …})

 

Note:

The function performs a query over an external data file. As a normal table sequence, the external file can be direcly 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 for the database query.

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

Parameters:

db

Use connect() to connect to a file system

sql

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

args

A parameter used in a simple SQL statement, which must be assigned value; multiple parameters should be separated by the comma.

Syntax of simple SQL:

with T as (x)

Define an external table through an esProc script; the query over the file x returns a table sequence or a cursor.

select x F,…

Select data from an external table where x is a field or an expression and F is the alias of the field.

from T T’

Use a defined external table T; T’ is the alias of this table and can be omitted.

from fn T

Direclty use file fn, whose format can be txt, csv, xls, xlsx, btx (a bin file) and ctx (a composite table file) as the external table T; the file is opened as a cursor, and by default has headers; use a relative path, which is relative to esProc main directory, or an absolute path to access file fn.

from {x}

 

as

x is an expression that can be executed within the current cell; the expression should be an esProc expression that returns a table sequence or a cursor

Can use as before the alias of an external file.

where

For conditional filtering.

join,left join,full join 

Associative operations that handle all data in the memory; the corresponding function is xjoin().

/* parallel (n) */

Perform a parallel processing when there isn’t a join and when the external file is a bin file/a composite table.

group by

For grouping operation with groups() function, whose result set is supposed to be able to be wholly stored in the mermoy.

group by n 

Perform grouping by the nth expression in the select statement; n should be a constant.

having

For filtering on grouped data.

order by 

For sorting operations in ascending order, by default.

order by n

Perform sorting by the nth expression in the select statement; n should be a constant.

distinct

Synonym of SQL DISTINCT clause; support only a single table and doesn’t support count( distinct ).

and, or, not, in ,is null, case when else end

between

Used in SQL style; in only supports sets of constants and doesn’t support subqueries

A data range sandwiched by two values, such as the value f1 that falls between 1 and 3, which can be written as f1 >= 1 && f1 <= 3

like

 

 

 

 

 

 

into fn

Fuzzy query, which supports the following wildcard characters:

%  Match multiple characters

_  Match a single character

[chracter,…] A character list that matches any single character in the list

[!chracter,…] or [^chracter,…] Won’t match any single character in the character list

Write the result set to file fn, whose format is determined by its extension, which can be txt, csv, btx, xls and xlsx; use a relative path, which is relative to esProc main directory, or an absolute path to access file fn

? and ?i 

Parameters in the simple SQL statement; i represents the ith parameter

insert

Append data to a file/vector; a composite table to which data is appended must be ordered by the dimension

update

Update data; only support composite tables and the WHERE clause must use the key

delete

Delete data; only support composite tables and the WHERE clause must use the key

commit

Updated data will be temporarily stored but won’t be executed until the commit action is performed; then the new data can be retrieved

rollback

Give up the latest update

aggregate functions

including sum, count, max, min and avg (without support for count(distinct) at present).

set operations

union, union all,intersect,minus; the computation is handled all in the memory

subquery

All subqueries are executed in memory except the from statement

top n

Get first n records

limit n offset m

Skip m records to get n records

Other functions

esProc functions conforming to the syntax

String functions

 

LOWER(str)

Convert into lower case

UPPER(str)

Convert into upper case

LTRIM(str)

Delete whitespaces on the leftmost side

RTRIM(str)

Delete whitespaces on the rightmost side

TRIM(str)

Delete whitespaces on both sides

SUBSTR(str,start,len)
SUBSTR(str,start)

Return a substring

LEN(str)

Return the length of a string

INDEXOF(sub,str[,start])

Return the position of a substring

LEFT(str,len)

Return the substring in the left of a specified string

RIGHT(str,len)

Return the substring in the right of a specified string

CONCAT(str1,str2)

Concatenate two strings

CONCAT(s1,s2,)

Concatenate multiple strings

REPLACE(str,sub,rplc)

Replace a substring by another string

Nemeric functions:

 

ABS(x)

Return absolute value

ACOS(x)

Return arc cosine

ASIN(x)

Return arc sine

ATAN(x)

Return arc tangent

ATAN2(x,y)

Return arc tangent

CEIL(x)

Return the smallest integer that is not less than x

COS(x)

Return cosine value

EXP(x)

Return the base of e to the power of x

FLOOR(x)

Return the biggest integer that not greater than x

LN(x)

Return the natural logarithm

LOG10(x)

Return the logarithm with base 10

MOD(x,m)

Return the remainder of x divided by m

POWER(x,y)

Return the value of x raised to the power of y

ROUND(x,n)

Return x rounded to n digits from the decimal point

SIGN(x)

Return the sign for x

SIN(x)

Return the sine value

SQRT(x)

Return the square root of x

TAN(x)

Return the tangent value

TRUNC(x,n)

Return x truncated to n decimal places

RAND(seed)

Return a random number

Date&Time functions:

 

YEAR(d)

Return the year

MONTH(d)

Return the month

DAY(d)

Return the day of the month

HOUR(d)

Return the hour

MINUTE(d)

Return the minute from d

SECOND(d)

Return the second

QUARTER(d)

Return the quarter from d

TODAY()

Return the date

NOW()

Return the current time

ADDYEARS(d,n)

Add years

ADDMONTHS(d, n)

Add months

ADDDAYS(d, n)

Add days

ADDHOURS(d, n)

Add hours

ADDMINUTES(d, n)

Add minutes

ADDSECONDS(d, n)

Add seconds

DAYOFYEAR(d)

Return the day of year

WEEKOFYEAR(d)

Return the calendar week of the date

Conversion functions:

 

ASCII(str)

Return numeric value of left-most character

CHR(n)

Return the chracter for integer n

INT(x)

Return an integer for string or number x

DECIMAL(x,len,scale)

Return the numeric value for a string or number

TIMESTAMP(str)

Convert the format string of yyyy-mm-dd hh24:mi:ss into datetime

NUMTOCHAR(d)

Return a string for number d

DATETOCHAR(date)

Convert date into a string in the format of  yyyy-mm-dd hh24:mi:ss

Others:

 

NULLIF(x1,x2)

Return null if x1=x2, otherwise return x1

COALESCE(x1,)

Return the first non-null parameter

COUNTIF(x1,,xn)

Count the number of data objects that meet the specified criteria

Return value:

A table sequence

Example:

 

A

 

1

=connect()

 

2

=A1.query("select * from Persons.txt")

3

=A1.query("select * from D:/Orders.txt")

4

=A1.query("select Id_P, sum(OrderNo) from Orders.csv group by 1 ")

Group by Id_P field

5

=A1.query("select Id_P, OrderNo from Orders.csv order by 2 ")

Sort by OrderNo field in ascending order, by default

6

=A1.query("select * into p1.txt from Persons.csv ")

Write the query result set to the file p1.txt

7

=A1.query("select * from Persons.csv where Id_P=? or Id_P>? ",2,2)

Retrieve records where Id_P is equal to or greater than 2

8

=A1.query("select * from Persons.csv where Id_P=?1 or Id_P>?2 ",2,2)

Retrieve records where Id_P is equal to or greater than 2; ?i represents the ith parameter

 

9

=A1.query("with persons as (file(\"D:/Persons.btx\").import@b()) select * from persons ")

Perform a query over Persons.btx and name the result set persons, and then perform a second query over persons; this is an optimization way for processing huge data in SQL

 

10

=A1.query("select /*+parallel(20)*/ count(*) from Persons.btx")

Count the records in the external table Persons.btx in parallel

11

=A1.query("select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P  ")

Perform a query through a join

12

=A1.query("select  distinct(Id_P),name  from Orders.txt  ")

Get records where Id_P values are unique

13

=A1.query(“select CASE  id_P  when 1  then 'one' when 2 then 'tow' else 'other'  end from p1.txt”)

Here the case when statement is used

14

=A1.query("select * from Persons.ctx  where city  like  'N%' ")

Use like syntax to find from the file Persons.ctx the record where the first character of the city field is N

15

=A1.query("select * from Persons.ctx  where Name  like  '_a_t%' ")

Use like syntax to find from the file Persons.ctx the record where the second character of the Name field is a

16

=A1.query("select * from Persons.ctx  where Name  like  '%[us]%' ")

Use like syntax to find from the file Persons.ctx the record where the Name field contains u or s

17

=A1.query("select * from Persons.ctx  where Name  like  '%[!a]%' ")

Use like syntax to find from the file Persons.ctx the record where the Name field doens’t contain character a

18

=A1.query("select * from Persons.ctx  where Name  like  '%[^s]%' ")

Use like syntax to find from the file Persons.ctx the record where the Name field doens’t contain character s

 

 

A

 

1

=connect()

 

2

=A1.query("select * from Dep.txt ")

3

=A1.query("insert into Dep.txt  values ('ABC',11)")

The insert statement appends data to Dep.txt

4

=A1.query("commit")

The appended data is temporarily stored and can’t be retrieved until the commit action is executed

5

=A1.query("select * from ctb.ctx")

6

=A1.query("insert into ctb.ctx  values (8,80)")

Append a record to the composite table

7

=A1.query("update ctb.ctx  set v1=20 where k1=2")

Update the composite table according to the key k1

8

=A1.query("delete from ctb.ctx  where k1=11")

Delete the record where k1 is 11 from the composite table, in which k1 is the key

9

=A1.query("delete from ctb.ctx  where k1=10")

Delete the record where k1 is 10 from the composite table, in which k1 is the key

10

=A1.query("rollback")

The rollback action gives up the latest update

11

=A1.query("commit")

The commit action

12

=A1.query("select * from ctb.ctx")

The updated composite table:

Since A10 performs a rollback action, the deletion of reocrd where k1 is 10 is canceled

 

 

A

 

1

=file("score1.txt")

 

2

=file("score2.txt")

 

3

=A1.cursor@t()

 

4

=A2.cursor@t()

 

5

=[A3, A4].mcursor@t()

 

6

=connect().query("select CLASS, max(SCORE)  avg_score  from {A5} where SUBJECT='math' group by CLASS")

Use from {x} syntaxt, where x is a cursor

 

r(T,F)

Description:

Find a certain field of a specified row in a table sequence/memory table.

Syntax:

k.r(T,F)

Note:

The function finds F field of row k in table sequence/memory table T, which is equivalent to T(k).F. Won’t check whether k is within T’s length.

Parameters:

k

Row number

T

A table sequence/memory table

F

A field of T; can be omitted

Return value:

A record

Example:

 

A

 

1

=demo.query("select EID,NAME from employee")

2

=1.r(A1,NAME)

Get the Name value in the first row of A1’s table sequence

3

=1.r(A1)

Since parameter F is absent, list all field values of the first row