Page 1 of 1

[Solved] Union query alternatives

PostPosted: Tue Aug 20, 2019 6:47 am
by gkick
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

Re: Union query alternatives

PostPosted: Tue Aug 20, 2019 3:02 pm
by Villeroy
"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).

Re: Union query alternatives

PostPosted: Tue Aug 20, 2019 4:56 pm
by gkick
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

Re: Union query alternatives

PostPosted: Tue Aug 20, 2019 5:18 pm
by Sliderule
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.

Re: Union query alternatives

PostPosted: Tue Aug 20, 2019 7:31 pm
by gkick
Thank you Sliderule, here s the code for your interest 2 out of 5 as all of them exceed the 128 limit

regards
GK

Re: Union query alternatives

PostPosted: Tue Aug 20, 2019 7:38 pm
by Sliderule
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