Union with QBSelect

Questions and answers regarding general SQL and backend databases

Union with QBSelect

Postby nevenam » Tue Feb 26, 2013 12:15 pm

Hi,
Do you plan to add the union operator to QBSelect? Is there a way to combine the result sets of select statements at the moment, besides using raw SQL statements?
Nevena
nevenam
 
Posts: 13
Joined: Tue Oct 09, 2012 4:53 pm

Re: Union with QBSelect

Postby rgansevles » Tue Mar 05, 2013 1:44 pm

Nevenam,

Servoy bases its sql generation on Hibernate which does not support unions.

You could try query with OR-ing 2 subselects to get the same result.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Union with QBSelect

Postby huber » Wed Feb 21, 2018 9:33 am

Hi

Is this statement of QB not supporting the UNION operator still valid as per today? A small example with OR-ing would be helpful.
rgansevles wrote:Nevenam,

Servoy bases its sql generation on Hibernate which does not support unions.

You could try query with OR-ing 2 subselects to get the same result.

Rob

Regards, Robert
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: Union with QBSelect

Postby rafig » Fri Nov 20, 2020 7:15 pm

Has anyone managed to get 'UNION' working with QB using 'or' option as stated here??
If so, please can you share your code?
If not, PLEASE can someone from Servoy share an example of how to do this?? (as was requested 2 years ago...)
Unfortunately, the online documentation is once again sorely lacking in certain useful examples, this being a perfect example...
I urgently need to be able to do this, as I'm having to use raw SQL instead, but that is not the 'best practice' way of doing things.
Have a good weekend
Thanks
Rafi
Servoy Certified Developer
Image
rafig
 
Posts: 704
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Re: Union with QBSelect

Postby ROCLASI » Sun Nov 22, 2020 5:20 pm

rafig wrote:I urgently need to be able to do this, as I'm having to use raw SQL instead, but that is not the 'best practice' way of doing things.i

I have to contest that using QB is 'best practice'. I know a lot of Servoy developers that don't use it, for good reasons.

QB has to cater to the lowest denominator as it tries to support all RDBMS vendors, which means it uses standard SQL with some support for dialect changes (TOP vs LIMIT, etc.).
And as we see in this thread it doesn't even do that as it doesn't support UNION/UNION ALL (which should have been named UNION DISTINCT/UNION, but that is another discussion altogether). But there is lot more not supported by QB that is in the SQL standard and supported by most if not all RDBMS vendors. What QB supports is in fact really really basic stuff.

Also SQL is code. I don't see the benefit of abstracting really readable code into something that is far less intelligible. Sure you get your warning markers when you misspell something. But that doesn't trump the benefits of using pure SQL. SQL is way easier to debug as you can simply drop it in any SQL editor and test it.

But QB makes my SQL more portable!
You have question yourself how many different RDBMS versions you need to support with your solution. It usually is not more then 2, a lot of the time it's just 1.

But what if we need to migrate to another RDBMS?
Consider this, using QB means you use standard SQL anyway. Odds are you can switch RDBMSs without changing any or very little SQL code if you would have used pure SQL.
And since you have full control of your code when using pure SQL you can also optimise your queries, because not all SQL is handled equally.

Once you learn the powerful features of the rich (standard) SQL language you will see that QB can't touch it. Common Table Expressions, Window Functions, UNION/UNION ALL, I use them all the time. And they all work well between the RDBMS vendors.
Again, SQL is code. And it's pretty standardised. As long as you stay away from the vendor specific syntaxes (they usually support the standard syntax too) your code can be highly portable.

Rafi, you say you urgently need to support UNIONS...I suggest you use pure SQL.


Just my (strong) opinion.
Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Union with QBSelect

Postby rafig » Sun Nov 22, 2020 7:18 pm

Hi Robert,
ROCLASI wrote:
rafig wrote:Rafi, you say you urgently need to support UNIONS...I suggest you use pure SQL.
Hope this helps.

Thanks for your reply.
I am completely with you and agree, and I am using SQL with 'UNION ALL', not QB (because I have no idea how to do it in QB due to lack of examples ;-) )
Apart from being able to move a solution from one back-end to another without having to change any SQL in Servoy code, QB understands data changes/broadcasting, so I think sometimes gives more up to date results based on any recent changes in data...
Let's see if someone can actually provide an example of doing UNION ALL using QB, it might help me and others...
Servoy Certified Developer
Image
rafig
 
Posts: 704
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Re: Union with QBSelect

Postby ROCLASI » Sun Nov 22, 2020 10:29 pm

rafig wrote:...QB understands data changes/broadcasting, so I think sometimes gives more up to date results based on any recent changes in data...

:?:
Why does QB need to know about data changes? It's a query language.
You load a FoundSet with it's primary key(s) and Servoy handles the rest. So it's because of the FoundSet that Servoy knows what caches to invalidate.
And if you use QB for DataSets, there is no data-broadcast and/or cache for DataSets.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Union with QBSelect

Postby Ruben79 » Mon Nov 23, 2020 4:32 pm

We have seen major performance increases when using QBSelect, due the fact that the query from a QBSelect does not get wrapped as a subquery by Servoy anymore: SELECT pk FROM table WHERE pk IN (<sql>); Instead it is executed directly.
Especially with big tables and/or complex WHERE clauses this very noticable.

In one occasion where I needed a UNION of completely unrelated tables, I just load 2 datasets and merge them in code (not advisable with really big datasets of course).

Code: Select all
var ds1 = databaseManager.getDataSetByQuery(query1, -1)
var ds2 = databaseManager.getDataSetByQuery(query2, -1)

var count2 = ds2.getMaxRowIndex()

for (var i = 1; i <= count2 ; i++) {
   ds1.addRow(ds2.getRowAsArray(i))
}
Ruben de Jong
Stb Software Development
SAN Partner

Stb Software Development - http://www.stb.nl
User avatar
Ruben79
 
Posts: 97
Joined: Wed Apr 18, 2007 12:43 pm

Re: Union with QBSelect

Postby rafig » Mon Nov 23, 2020 4:44 pm

Hi Ruben,
thanks for your reply...
Ruben79 wrote:We have seen major performance increases when using QBSelect, due the fact that the query from a QBSelect does not get wrapped as a subquery by Servoy anymore: SELECT pk FROM table WHERE pk IN (<sql>); Instead it is executed directly.
Especially with big tables and/or complex WHERE clauses this very noticable.

That's great to hear, another good reason for using QB...
In one occasion where I needed a UNION of completely unrelated tables, I just load 2 datasets and merge them in code (not advisable with really big datasets of course).

Thanks for the idea & code, I may well try that...
But what do you consider 'really big datasets'?? (my UNIONs return about 6k rows in total, might be more or less in production...)

Thanks
Rafi
Servoy Certified Developer
Image
rafig
 
Posts: 704
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Re: Union with QBSelect

Postby Ruben79 » Tue Nov 24, 2020 6:48 pm

Hi Rafi,

Nothing can beat the performance of the union being done in the sql itself and fetched in one dataset, that is clear.
Also my example of the query being wrapped as a subquery only applies when loading foundsets. It does not happen for datasets as far as I know.

But if you really want to use QBSelect for various reasons this is just an option.

I consider 6K records being not that much, but it depends a bit.. In general it just takes the time to execute both queries, fetch the dataset and then some cpu time to merge the dataset. Also depending how big the rows are, etc.
Is this smart client or NG client? NG client is a lot faster with loading datasets because less overhead and no transport needed from server to client.

It's just a matter of trying it out what works best in your case. QBSelect can be a nice tool and we use it by default especially with loading foundsets, but plain SQL sometimes is the best (or only) option.
Ruben de Jong
Stb Software Development
SAN Partner

Stb Software Development - http://www.stb.nl
User avatar
Ruben79
 
Posts: 97
Joined: Wed Apr 18, 2007 12:43 pm

Re: Union with QBSelect

Postby rafig » Tue Nov 24, 2020 7:14 pm

Hi Ruben,
thanks for your reply.
For the moment I'm going to stick with the SQL/UNION as it works (& it's NG client)

(Still looking forward to seeing an example of QB with OR to replace SQL/UNION...)
Servoy Certified Developer
Image
rafig
 
Posts: 704
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Re: Union with QBSelect

Postby ptalbot » Thu Nov 26, 2020 12:46 am

ROCLASI wrote:I have to contest that using QB is 'best practice'. I know a lot of Servoy developers that don't use it, for good reasons.


100% agreed! :D

The QB syntax is atrocious and when trying to add joins from many tables you get all warnings for unrecognized colums, so you have to add @type annotations and intermediate variables. In the end, the resulting code is hard to read, which definitely defeats the purpose.

As usual, YMMV ;)
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1654
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 7 guests