[Solved] Union query alternatives

Creating tables and queries

[Solved] Union query alternatives

Postby gkick » Tue Aug 20, 2019 6:47 am

Hi,

Having a bit of a problem either with my logic and or putting multiple query outputs into a union query and sort the result of the union by a date field.
Previously the union based on queries worked as none contained calcs, listed all but dates unsorted for the union but sorted as per individual source query.

Now the input querys contain dateadd and datediff calcs, the datatypes and sequence of fields is identical, however the calcs per query are not identical and
I get the 'only quries are allowed error'.

One alternative way I can think of is to to a maketable query and then append the output of all the input queries, however that seems to be pretty elaborate as it would
require constant deletes and re append.
Any ideas ?

Attached a screendump - another possibility might be to hide the column headings in the tablecontrol, but there does not seem to a property to do this.

Thanks for your thoughts or ideas
Attachments
combine these into one.JPG
Last edited by gkick on Sun Aug 25, 2019 9:46 am, edited 1 time in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 172
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Union query alternatives

Postby Villeroy » Tue Aug 20, 2019 3:02 pm

"Only queries are allowed " indicates that Base can not handle the given SQL statement.
Turn on SQL mode (you already have if you compose UNIONs) and then menu:Edit>"Run SQL directly". Base will pass through the SQL string to the database engine and waits for the returned result set or errors. Direct SQL has some disadvantages. Base can not link a form and a subform with parameters. If one of the forms is run in direct SQL, the subform will inclide all records regardless of the parent form's current record (if I recall correctly).
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.x
User avatar
Villeroy
Volunteer
 
Posts: 27554
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Union query alternatives

Postby gkick » Tue Aug 20, 2019 4:56 pm

Thanks, yep there are limitations grrr, just on another note managed to create new users however I can not get the login prompt because the macro in the magic folder is set to SA password false, what do I ned to change in the FreeHSQLDB -Button Relocate to throw up the login prompt? Not familiar with the syntax.
But man this folder is wonderful, tried it on another machine, works like a treat.
Once again thank you very much

GK
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 172
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Union query alternatives

Postby Sliderule » Tue Aug 20, 2019 5:18 pm

You have NOT posted the UNION Query ( SELECT statement ) you are attempting to run. You just posted a graphic. Therefore, any help is not possible.

Yes, you can run a Query with UNION OR UNION ALL command with OpenOffice / LibreOffice Base, either WITH OR WITHOUT the Base Parser. See the post below from 2017-06-26 by user Sliderule.

UNION or UNION ALL Query with OpenOffice / LibreOffice Base

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1196
Joined: Thu Nov 29, 2007 9:46 am

Re: Union query alternatives

Postby gkick » Tue Aug 20, 2019 7:31 pm

Thank you Sliderule, here s the code for your interest 2 out of 5 as all of them exceed the 128 limit

regards
GK
Attachments
2of5.JPG
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 172
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Union query alternatives

Postby Sliderule » Tue Aug 20, 2019 7:38 pm

I will have NOTHING to due with a GRAPHIC IMAGE of a Query Statement.

Sliderule wrote:You just posted a graphic. Therefore, any help is not possible.


Perhaps someone else can help you.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1196
Joined: Thu Nov 29, 2007 9:46 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest