cancel
Showing results for 
Search instead for 
Did you mean: 

Unsupported combination: union + subqueries (other databases run ok)

Former Member
0 Kudos

Why doesn't MaxDB support union between queries when one of them has a subquery on the column list?

Example:

select (select dummy from dual) from dual

union

select (select dummy from dual) from dual

This also does not run:

select (select dummy from dual) from dual

union

select dummy from dual

Oracle runs these two examples ok.

SQL Server also supports this kind of query.

Even MySQL supports it (i've just tested it while writing this topic).

This limitation is causing me some problems, because we have a multi-database ERP application, and the developers are always complaining. I'm kind of the "SAPDB/MaxDB advocate" here, and asking developers to rethink reports, write workaround code and redesign queries doesn't help a lot.

Except this and some other "workaroundable" problems, i'd like to report that we're getting very good success on deploying our Healthcare ERP with SAPDB/MaxDB, on about 20 facilities, ranging from 5 to 150 simultaneous users, and database sizes up to 35GB at this moment.

Best regards for all!

Viktor Spinola (Brazil)

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

>

> Why doesn't MaxDB support union between queries when one of them has a subquery on the column list?

I'd guess our developers just didn't implement it, because this kind of query is not used by SAP applications.

> This limitation is causing me some problems, because we have a multi-database ERP application, and the developers are always complaining. I'm kind of the "SAPDB/MaxDB advocate" here, and asking developers to rethink reports, write workaround code and redesign queries doesn't help a lot.

Ok, although this kind of query surely runs on other DBMS it's very likely that they will cause some kind of performance issues.

May we know what the usecase for you is for this kind of queries?

Maybe we can work out a better alternative.

> Except this and some other "workaroundable" problems, i'd like to report that we're getting very good success on deploying our Healthcare ERP with SAPDB/MaxDB, on about 20 facilities, ranging from 5 to 150 simultaneous users, and database sizes up to 35GB at this moment.

Nice to hear that.

It's a fine example that shows again that it's not always necessary to use expensive high-end DBMS technology to create successful applications.

MaxDB really does offer a lot here for free.

regards,

Lars

Former Member
0 Kudos

Thank you, Lars.

The use for this kind of queries is mainly for reports. We have some really complex reports, and this kind of query is needed sometimes. About the performance issue, I feel that the existence of the union is not such a big factor. Not all reports run within seconds, after all... we have some longer-running queries through the application. And some "union" reports run really fast, also.

I'd like to know more about the policy of SAP for this kind of situations: when a problem exists in MaxDB and it doesn't affect SAP apllications. When we made our option for SAPDB, around 2002/2003, it was being released under the GPL, and we foresaw a community arising around it, which is important for this kind of situation: diversity of requirements, etc.

The license has changed. Initially, this change rised some concerns (and topics on this forum ), but, after some time, and witnessing the constant evolution of the product, we're ok with the new terms.

But now it's important for us to know how important for SAP is the building and maturing of this community of non-SAP MaxDB users. Every time we find some minor bug or situation like this, the thinking that crosses our minds is exactly this that we're talking about: "hmm... seems that SAP systems don't do this kind of query...". We write workarounds, and sometimes post the issue here in the forum... kind of hoping that the development team considers it important. (or maybe that SAP systems begin to demand it )

Again, thanks a lot for providing the market with such a good product!

Viktor

lbreddemann
Active Contributor
0 Kudos

> The use for this kind of queries is mainly for reports. We have some really complex reports, and this kind of query is needed sometimes. About the performance issue, I feel that the existence of the union is not such a big factor. Not all reports run within seconds, after all... we have some longer-running queries through the application. And some "union" reports run really fast, also.

Well of course UNION is not per se a problem.

However a simple UNION always add row unification to the result - so there is additonal effort done here.

Also, the query optimizers have to master more complex problems by using this approach.

> I'd like to know more about the policy of SAP for this kind of situations: when a problem exists in MaxDB and it doesn't affect SAP apllications. When we made our option for SAPDB, around 2002/2003, it was being released under the GPL, and we foresaw a community arising around it, which is important for this kind of situation: diversity of requirements, etc.

Please check the [SDN Wiki FAQ|http://wiki.sdn.sap.com/wiki/display/MaxDB/FAQ] on these questions.

Concernig the "diversity of requirements" I don't see that this was there and I don't see that this will change.

SAP products are and have been effectively the only requirement holders that took influence to the product development of MaxDB.

Of course this is due to the stakeholder situation nowadays.

But it had been the same when MaxDB was OpenSource.

There had been no inputs from the community that have made it into the main product.

> But now it's important for us to know how important for SAP is the building and maturing of this community of non-SAP MaxDB users. Every time we find some minor bug or situation like this, the thinking that crosses our minds is exactly this that we're talking about: "hmm... seems that SAP systems don't do this kind of query...". We write workarounds, and sometimes post the issue here in the forum... kind of hoping that the development team considers it important. (or maybe that SAP systems begin to demand it )

Well I guess (I'm not part of the development team, so I can only guess), it's not a decision of the developers anymore.

It's all about priorities of tasks and goal fulfillment on managment level.

With methodologies like SCRUM there is barely space for "in-between" changes/fixes or enhancements.

Just my two pence on this.

Lars

Former Member
0 Kudos

Thanks, Lars!

Your response was useful for clarification of the strategy!

Regards!

Answers (0)