Page History

...

Example SQL queries

There are a number of SQL queries that can be used to retrieve data from the database. Only (read-only) statements are supported. Statements must be formatted as:

SELECT [DISTINCT] <select_expr> FROM TABLE_NAME [WHERE <where_condition>] \[ORDER BY COLUMN_NAME [ASC |DESC]\]

<select_expr>: (* | <COLUMN_NAME , COLUMN_NAME, ...)

<where_condition>: COLUMN_NAME <operator> AND <where_condition> \ OR <where_condition> \ LIKE <where_condition>

<operator>: (= | <> | < | > <value>) | BETWEEN <value> AND <value>

For the Locations, Parameter and Filters table the SQL Query "Select * from <TableName>" is allowed. For the TimeSeries Table this query will return an error.

A valid query for the TimeSeries Table is as follows:

SELECT * from TimeSeries
WHERE moduleInstanceId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND valueType = 'scalar'
AND time BETWEEN '2008-12-19 12:00:00'  AND '2008-12-23 12:00:00'
AND timeSeriesType = 'external forecasting'
AND timeStep = 'unit=hour multiplier=6'

Or, when using filter id's:

SELECT time, value from TimeSeries
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00'  AND '2008-12-23 12:00:00'

Example Locations queries

SELECT name, y,x from Locations ORDER BY name DESC
SELECT name, y,x from Locations WHERE X > '161000'
SELECT * from Locations where id = '46DP0003' OR id = '46DP0004'
SELECT name from Locations WHERE name <> 'Meerselsche Peel (WAM)'
SELECT id, name, y, x from Locations WHERE id LIKE '254%'
SELECT id, name, y, x from Locations WHERE name LIKE '%STUW%' or name LIKE '%Gemaal%'

Example Filters queries

Return all location and parameter combinations from a specific filter

SELECT id, locationid, parameterid FROM filters WHERE id = 'ImportSHEF' ORDER BY location

Image Added

Return all locations from a specific filter

SELECT DISTINCT locationid FROM filters WHERE id = 'ImportSHEF'

Image Added

Return a list of the main filter groups

SELECT DISTINCT id FROM filters WHERE issubfilter = false

Image Added

Miscellaneous

Using a different port number (available 200901)

...