OQL from sub-query

1
I was trying out an OQL from-sub-query as per the syntax published in your documentation : https://docs.mendix.com/refguide8/oql-from-clause   I tried out a query like the following - SELECT LogSummary.* FROM ( SELECT * FROM AuditTrail.Log AS Log LEFT OUTER JOIN Log/AuditTrail.LogLine_Log/AuditTrail.LogLine AS LogLine LEFT OUTER JOIN LogLine/AuditTrail.ReferenceLog_LogLine/AuditTrail.ReferenceLog AS ReferenceLog LEFT OUTER JOIN ReferenceLog/AuditTrail.ReferenceLogLine_ReferenceLog/AuditTrail.ReferenceLogLine AS ReferenceLogLine LEFT OUTER JOIN Log/AuditTrail.Log_User/System.User AS SystemUser LEFT OUTER JOIN TaskSvcModule.User AS TsUser ON SystemUser.Name = TsUser.AID_XID WHERE ( Log.ReferenceId IN ( '3940649673965793', '3940649673965794' ) OR Log.ReferenceId IN ( SELECT CAST ( TaskApproval.TaskApprovalId AS STRING ) FROM TaskSvcModule.Task AS Task JOIN Task/TaskSvcModule.TaskApproval_Task/TaskSvcModule.TaskApproval AS TaskApproval WHERE Task.TaskId IN ( '3940649673965793', '3940649673965794' ) ) ) AND Log.changedDate != null AND ( ( LogType = 'Add' AND LogLine.Member = 'TaskId' AND LogLine.OldValue = '' AND LogLine.NewValue != '' ) OR ( LogType = 'Change' AND LogLine.Member = 'TaskSvcModule.Task_TaskState' AND ReferenceLog.Operation IN ( 'Added', 'Deleted') AND ReferenceLogLine.Member = 'Name' ) OR ( LogType = 'Change' AND LogLine.Member = 'CompletionDate' AND LogLine.NewValue != '' ) OR ( LogLine.Member = 'TaskSvcModule.TaskApproval_TaskStateTransition' AND ReferenceLog.Operation = 'Added' AND ReferenceLogLine.Member = 'Action' ) ) ) AS LogSummary I get an error  : An error occurred while executing OQL: 'audittrail$log' is not a valid entity type.   I can execute just the sub-query directly, without any issues. But when I try to nest it within a sub-query, it fails. I’ve also tried the above inner query, without the alias : SELECT * FROM (   /* inner query */ )   In fact even something basic like the foll. gives the same error : SELECT LogSummary.* FROM (     SELECT *     FROM      AuditTrail.Log  ) AS LogSummary   Without the alias too, it’s the same error : SELECT * FROM (     SELECT *     FROM      AuditTrail.Log  )    What is the correct syntax for an OQL from-sub-query?
asked
1 answers
1

Mendix support subquery in select clause and from Clause, subquery return only one column it will not return multiple columns

Syntax as below
select c.Name, count(o.TotalPrice) c,
(select FirstName from "Sales.Customer" d where d.Name = c.Name) w
from "Sales.Customer" c
left join "c/Sales.Order_Customer/Sales.Order" o
group by c.Name
having count(o.TotalPrice) > 1

You can check above query in this portal:  
https://mydemoversion8-sandbox.mxapps.io/p/OQL

answered