[Solved] SQL Syntax for SQL Newbie

[gkick]

Hi,
I have a HSQLDB 2.5 split backend. In my database there are several tables like docs, contracts, appointments. All these tables have in common is an expiry date field and a remind me x days prior to.
The aim is to use a union query to produce all those forthcoming expiries within the next 60 days.
I am ok so far with the datadd function
SELECT "expiry","alert", DATE_ADD ( "expiry", "alert" day ) AS "Flag" FROM "tblTest" although need to find a way to make the alert a negative. Now where I am stuck is the syntax for the date range as the between and in the gui only accepts valid dates but not field names. And to show my ignorance what would be the best way to implement the whole lot, at the moment I can only think of multiple subqueries to be used for the final union query.
Hope this makes sense.
Thanks for your thoughts

Gerhard Kick

[Villeroy]

Try DATE_ADD ( "expiry", -"alert" day ) if you want to subtract the alert number from the expiry date.

Subqueries are simple.

Code: Select all

SELECT "MyTable"."A", "MyTable"."B", "Q"."X", "Q"."Y"
FROM "MyTable", (SELECT <anything else> FROM "SomeWhere") AS "Q"
WHERE "MyTable"."ID" = "Q"."XID"

You simply create a working query, put it in braces, add an alias and treat this just like another table in the FROM clause.
Alternatively, you store the query as a separate query and use it just like another table:

Code: Select all

SELECT "MyTable"."A", "MyTable"."B", "Q"."X", "Q"."Y"
FROM "MyTable", "OtherQuery AS "Q"
WHERE "MyTable"."ID" = "Q"."XID"

where "OtherQuery" is the embraced statement in the first example.
The FROM clause specifies row sets to select columns from. Tables and SELECT statements provide row sets.

Caveat: In rare cases where you need to run some query in direct SQL mode (because Base can not handle it correctly but HSQL can), any SELECT statement stored as a query won't be recognized because the database engine has no view on the calling Base frontend. In this case you have to either embed the SELECT in braces or store it as a view. Views are SELECT statements stored in the realm of the database engine (namely in the HSQL script file). Queries are stored in the realm of the frontend (in the Base document).

[gkick]

Thanks Danke