Pass a list to Execute OQL Statement

0
Hi all,  Is it possible to pass a list to “Execute OQL Statement” to use the list for FROM Clause?   I want a record (which I group by) with full information. I’ve tried a OQL Clause in OQL Playground and got error that seems to say “sub query is not allowed after “JOIN”” “SELECT cust.name, cust.birthday FROM  Sales.Customer as cust      RIGHT JOIN (           SELECT max(FirstName) as first            FROM Sales.Customer            group by CustomerType) as nameselected      ON cust.firstname=nameselected.first ”   I’m thinking to split the oql in two, ①the one does the “group by” 　　　　”SELECT max(FirstName) as first            FROM Sales.Customer            group by CustomerType” ②the other one uses the first oql result and “select the specific record”. “SELECT cust.name, cust.birthday FROM  Sales.Customer as cust      RIGHT JOIN (RESULT of ①) as nameselected      ON cust.firstname=nameselected.first ” unfortunately OQL Error returned with the method above….   Does anybody have any idea? All comments and reply are welcome. Thank you in Advance. Yuki   https://mydemoversion8-sandbox.mxapps.io/p/OQL https://docs.mendix.com/refguide8/oql-from-clause
asked
1 answers
2

The subquery can only be added at the left side of the join. So switch them around and you should be one step further. This will get you the set of customers and their firstname:

FROM
(FROM Sales.Customer
 group by CustomerType
 SELECT max(FirstName) as FirstName, max(Birthday) as Birthday) as nameselected
inner join Sales.Customer as cust
ON cust.firstname=nameselected.FirstName 
SELECT *

But it seems impossible to get it to show two columns. It only shows the first column of the subquery. Never the firstname and the birthday as well.

A good alternative is using the subquery’s result as a list, which is also easier on the eyes and you get to use all query-options in your subquery:

SELECT cust.Name, cast(cust.Birthday as string) as Birthday
FROM  Sales.Customer as cust
     WHERE cust.FirstName IN (
          SELECT max(FirstName) as FirstName
          FROM Sales.Customer 
          group by CustomerType)

Casted the birthday for readability.

answered