MDO SELECT Query

[Former Member]

Hi,

When I try to do select query on MDO objects, I could see there are many restriction in executing the SELECT statement unlike doing with traditional database.

For example, I am not able to use any of the aggregate function (like sum, count, average) and other select function like SELECT SINGLE, SELECT DISTINCT,,, etc.

I know we could accomplish the above function by getting the data into BLT. But is there is any other way we could execute MDO query directly for this functions?

Thanks

Mahesh

[Former Member]

Yes Mahesh,

We cant add any SQL functions in the select statements in an MDO.

But if you want to still use them, there is an alternative though not an easy one.

You can connect the MDO instance in your SQL DB and then access the MDO object as a SQL table. Here you can apply all the SQL functions to your MDO object and you can even use them in your fixed query.

Disadvantages:

1.The MDO created in the DB will not be the same name as in your Workbench but rather a system defined.

2. The field names will not be the same as that defined in MDO, but rather field1,field2 and so on... So you need to work with the sequence rather than the names.

As i said a bit painful but can be used in case you dont have a workaround. I hope it helps.

Regards,

Satpreet Singh

[Former Member]

Thanks Mike and Satpreet Singh for your input.

This helps a lot.

Thanks

Mahesh

[former_member4529]

Hi,

I was wondering what is the purpose of the Group Expression in MDO query when it is not possible to use any aggregate function with the selected columns? Can anyone throw some light on this?

Thanks,

Dipankar

[jcgood25]

A quick test with an MDO Select with Northwind customer data in it allowed me to use COUNT(*) AS NumCustomers as a custom attribute column and [Country] in the group by expression box.

[former_member4529]

Thanks Jeremy! Yes I have also noticed that only COUNT() works but whenever any MDO attribute name is used in the aggregate function it throws an error. So is it Group Expression only for COUNT() here? Is there any plan to support SQL aggregate functions with attribute/column names (e.g. SUM(Quantity)) in MDO query in future?

Thanks,

Dipankar

[agentry_src]

Hi Mahesh,

You are correct and this is why MDO is not going to magically replace relational databases.

You can push the aggregate functions over to the data source with custom views and use MDO to extract against them. Or stick with regular SQL Query Action Blocks where applicable.

Regards,

Mike

[Former Member]

Mike,

Thanks Mike for your input. The reason we are planning to use MDO against database is easy to implement and maintain.

Based on your comment, I hope we will miss so much of the functions by using MDO.

Is there is any reason why SAP has not included Fixed Query Mode for MDO?

Thanks,

Mahesh

[agentry_src]

Hi Mahesh,

MDO is still pretty new, so I do not know why it does not work the way you would desire. Since MII 12.2 is not yet GA (I don't think so, but soon?), it is hard to say what additional capabilities will be added or incorporated in future releases.

But MDO is a really handy tool for maintaining data from ECC for disconnected operations. Say you need a list of production orders for your factory operations. Set up an MDO query using a BAPI like BAPI_PRODORD_GETLIST. The MDO will run and maintain that list even when SAP ECC is unavailable (for weekly maintenance, for example). MDO still has the latest retrieved values cached and will update the list once ECC becomes available again (depending on the MDO configuration for schedule, retries, etc.)

Regards,

Mike

Edited by: Michael Appleby on Jun 24, 2011 6:31 PM