4.1.2 Basic use of SQL statements

Read(178) Label: query, sql,

Using db.query() function can execute SQL commands in a specified database. A SQL statement could contain various subqueries and database functions.

 

A

1

=connect("demo")

2

=A1.query("select * from STATES where ABBR like 'N%' order by POPULATION desc")

3

>A1.close()

A2 finds states whose abbreviations begin with N, and sorts them by population in descending order. Result is as follows:

A SQL statement can also use cellset data as parameters:

 

A

1

=connect("demo")

2

[CA,ME,NM,SC,LA]

3

=A1.query("select * from STATES where ABBR in (?) order by AREA",A2)

4

>A1.close()

A3 finds states whose abbreviations are contained in the specified sequence and sorts them by area in ascending order. Result is as follows:

In particular, query function will return only the first-found record by using @1 option with it. In this case, the query result will be returned as a sequence comprising field values of the first record. If the record has only one field, a single value will be returned. For example:

 

A

1

=demo.query@1("select * from CITIES")

2

=demo.query@1("select NAME from CITIES where STATEID=5")

Here are results of A1 and A2 with @1 option added:

 

Note that the return value is a single value or a sequence, instead of a table sequence, by using this option.