[Solved] SQL Syntax for SQL Newbie

Creating tables and queries

[Solved] SQL Syntax for SQL Newbie

Postby gkick » Wed Aug 07, 2019 5:41 pm

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
Last edited by gkick on Thu Aug 15, 2019 8:54 am, edited 1 time in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 245
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: SQL Syntax for SQL Newbie

Postby Villeroy » Wed Aug 07, 2019 6:10 pm

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

Subqueries are simple.
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28422
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL Syntax for SQL Newbie

Postby gkick » Wed Aug 07, 2019 7:04 pm

Thanks Danke :D
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 245
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests

cron