[GRASS-user] SQL query

[Glynn Clements]

Hamish wrote:

> I have a bunch of maps in the same mapset with identical table structure
> and with similar sounding names. With v.to.db I've uploaded a distance to
> each of their 'length' columns.
> 
> The DB is SQLite and everything is stored in $MAPSET/sqlite.db.
> 
> Now I want to print them all out in a single command.
> 
> I tried this, but it doesn't work:
> 
> G65> db.select sql="SELECT length FROM table_name LIKE 'track_%'"
> 
> DBMI-SQLite driver error:
> Error in sqlite3_prepare():SELECT length FROM table_name LIKE 'track_%'
> near "'track_%'": syntax error
> 
> 
> 
> this works:
> 
> db.select sql="SELECT length FROM track_001 \
>          UNION SELECT length FROM track_002 \
>          UNION SELECT length FROM track_003"
> 
> but I have something like 100 maps and the statement would get too huge.

Tough ;)

SQL doesn't allow you to perform an "implicit union" on multiple
tables. You need to perform an explicit union, and there isn't any
kind of "wildcard union" construct; IOW, you need an occurence of the
SELECT keyword for each table.

If the statement is too large for db.select's sql= option (or for the
underlying driver), you can use a hierarchical approach, creating
intermediate results from as many tables as can be merged at a time
with "CREATE TABLE AS SELECT ...", then merging the intermediate
results. Or you can use an iterative approach, starting with an empty
table then executing e.g. "INSERT INTO result SELECT length FROM ..." 
for each table in turn.

-- 
Glynn Clements <glynn at gclements.plus.com>