4.1.2 Basic use of SQL statements
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.