A query for 73 tables

[frankstr]

LS
I did create a db with 73 tables. now I want to make a querie for all tables involved. All tables do have a date column. I want to filter only the dates of yesterday.
Is there a clear how to?

[Villeroy]

To be run in "direct mode" (query-menu:Edit>Run SQL directly).

Code: Select all

SELECT "Field1", "Field2","Field3" FROM "Table1"
UNION SELECT "Field1", "Field2","Field3" FROM "Table2"
UNION SELECT "Field1", "Field2","Field3" FROM "Table3"
UNION SELECT "Field1", "Field2","Field3" FROM "Table4"
[...]

returns a read-only row set if each SELECT selects the same count of fields with matching field types at the respective position. Even if the fields in "Table3" are not named as "Field1", "Field2" and "Field3", they need to share the same types in the same order as the 3 fields in the preceeding SELECT

[frankstr]

hi villeroy,
Did as you said.
The querie returned all the rows of the table and I only want the table to return the row entered every yesterday.

[frankstr]

LS
Tried expression UNION SELECT. Got the fault message that this is not allowed in a querie.

[eremmel]

You can add to each SELECT a WHERE clause and filter on the dates that are yesterday. Date manipulation can be database specific, so you have to figure out how to get today, e.g some databases support the function TODAY() or NOW() and compare it with an interval. The following is for sure not the best approach but it saves on the interval calculation and the trouble with time-part in dates, so something like this might work (works in msaccess):

Code: Select all

...WHERE year(now())=year(date1) AND month(now())=month(date1) AND day(now())-1=day(date1)

Note that the UNION statement proposed by Villeroy filters out duplicate rows that are returned among the tables. When you do not want that use UNION ALL.

[Villeroy]

Oh, thank you for the UNION ALL. I was not aware of this.
Your condition (Day(now())-1 = Day("Date") will fail on every 1st day of a month because there will be no date with day #0.

Unfortunately there is no concept of "yesterday". Neither Base nor the underlying HSQLDB engine has a function to return a date value other than today's date.
First, I would store the UNION query as a view. Simply copy the SQL-string, call main-menu:Insert>View(simple), paste the string, turn on the "direct SQL" mode and save the thing as a view in the tables container rather than queries.
Now you have more options reuse this thingy as if it were a read-only table.

The following query selects the "Date" and it's peceeding day from an assumed view "vUnion":

Code: Select all

SELECT "Date", CAST ( YEAR( "Date" ) || '-' || MONTH( "Date" ) || '-' || ( DAY( "Date" ) - 1 ) AS "DATE" ) AS "Yesterday" FROM "vUnion"

Warning: This tends to fail under certain conditions. It works well with calculated years and months and subtracting one day seems to work with some values I tested (2000-1-1, 1999-1-1, 2004-3-1 plus some arbitrary dates).

You can also

Code: Select all

SELECT * FROM "vUnion" WHERE "Date"<CURRENT_DATE ORDER BY "Date" DESC

select the top-most date, which should be yesterday, and apply auto-filter.

And then there are param-queries in parsed mode rather than direct mode:

Code: Select all

SELECT * FROM "vUnion" 
WHERE Year("Date")=:Y
AND Month("Date")=:M
AND Day("Date")=:D

[eremmel]

The remark of Villeroy about failing on DAY(date1)-1 is indeed true, a slip of my typing. But what usual works is that a date-type +/- and integer value results in an date-type again with +/- some days. So this might work to match yesterday:

Code: Select all

...WHERE year(now())=year(date1) AND month(now())=month(date1) AND day(now()-1)=day(date1)

Note the move of -1.

[Villeroy]

Adding integers to dates and/or times does not work (inapropriate data type error).
Furthermore, SQL in Base requires double-quotes or backticks around object names.

[frankstr]

About union select:
Tried:

Code: Select all

SELECT "ID", "Notities" FROM  "table1" 
UNION ALL
SELECT "ID", "Notities", "datum" FROM "table2" 

Then got the message that UNION ALL is not a SELECT argument.

Is there something wrong with the code above?

[Villeroy]

You forgot to use "direct SQL".

[frankstr]

OK Villeroy,
I'm new to this kind of database stuf but i'm seeing now what you mean.

[Villeroy]

There are 2 different applications you are working with.
If your database's status bar shows something with HSQLDB, the database is desribed at http://hsqldb.org
And then there is Base which connects the underlying database to the OpenOffice tool set plus offering some primitive and incomplete GUI for database manipulation and query design.
The queries you create in the graphical designer are parsed (interpreted) in Base, you can switch between graphical view and textual SQL-view.
When you turn on "direct mode" you tell Base to ignore the SQL command, it's content and it's syntax. The SQL-string gets passed through to the database which interpretes and responds to it. The results (or error messages are passed back) to Base.
UNION is one of the standard SQL-features that are not supported in Base, but the underlying databse application knows well what it means.

IMHO, the biggest disadvantage of Base is, that it tries to hide away technical details without being mature enough. You reach the point very soon where you need to know what's going on under the hood. Otherwise you are lost in useless options and meaningless error messages.

[Tutorial] Structured Query Language in Base

[eremmel]

Just to come back on the yesterday issue. I looked to the functions that are supported by HSQLDB and the following might work for you:

Code: Select all

... WHERE DATEDIFF('dd',"Date",CURRENT_DATE) = 1 

[Villeroy]

Just to come back on the yesterday issue. I looked to the functions that are supported by HSQLDB and the following might work for you:

<Slap Head>
I'm glad to feel like a fool. Yes, the mere comparison with yesterday works this way. We do not need to retrieve yesterday's actual date value.