Failed to execute OQL with sub_oql_query, Can anyone help?

0
I am evaluating OQL, tried following OQL, I can successfully execute sub_oql_query, but when I put into from clause, I got following errors, It is strange.  Caused by: com.mendix.connectionbus.ConnectionBusRuntimeException: 'sales$order' is not a valid entity type., exception occurred on mapping the following query: SELECT sales$order.totalprice AS TotalPrice, sales$order.date AS Date, sales$order.ispaid AS IsPaid FROM sales$order WHERE sales$order.totalprice > ?     at com.mendix.connectionbus.retrieve.query.mapping.QueryMapper.getMappedQueries(QueryMapper.java:276) Caused by: com.mendix.connectionbus.ConnectionBusRuntimeException: 'sales$order' is not a valid entity type SELECT TotalPrice,Date,IsPaid FROM   (SELECT Sales."Order".TotalPrice AS TotalPrice,           Sales."Order".Date AS Date,           Sales."Order".IsPaid AS IsPaid    FROM Sales."Order"    WHERE Sales."Order".TotalPrice>50)
asked
7 answers
0

Even this OQL gives the same error:

Select Name from
(SELECT * FROM Sales.Customer)

Regardless of any surrounding double-quotes. It just seems to be not possible to use any sub_oql in the FROM part.

Therefor, this must be a bug in the OQL-module. Try getting in contact with the OQL-module creator. See if he can fix this.

answered
3

I think you need to add a “as t” or simliiar name then ‘t’ for the sub query.

answered
2

Following is the feedback from Mendix team

 

I can inform you that the OQL query you are using is not supported:

select T.Name as Name from (select Name from Sales.Customer) as T

The restriction refers to the top level query. You can only do select * from (select ...). 
Please note that the restriction only applies if the subquery occurs in the from clause, subqueries in select or where clauses are not affected.

We are checking to see if there is another way to do this OQL query or if it is on the roadmap to be added.

answered
0

Why are you making a subquery, with same result'?

Why are you using the app name? instead of Sales."Order".TotalPrice use Order.TotalPrice

answered
0

Mendix R&D is investigating this problem. I will keep this topic updated

answered
0

You sub query statement is wrong, should have a naming after the closing bracket. for example:
Select Name from (SELECT * FROM Sales.Customer)A

answered
0

I have the same problem! I simple “subquery” statement doesn’t work:

select 
    a.fee as fees

from

    (SELECT facts.Fee as fee FROM MyModule.cost_facts as facts ) as a
;

There are any updates or solutions?
Thanks.

answered