How to retrieve data from two different entities that are located in two different modules and have a one-to-many association, using OQL?

0
I need retrieve data from two entities that are located in two different modules and have a one-to-many association (ManySuggestion-To-OneHeadquater), using OQL in a microflow. My query looks like this: SELECT Sug.IdSuggestion AS IdSuggestion , Sug.NameSuggestion AS NameSuggestion, Sug.Status AS Status, head.DescHead FROM Core.Suggestion AS Sug JOIN Core.Suggestion_Department/ImportData.headquarter AS head But I got an error: Caused by: com.mendix.modules.microflowengine.MicroflowException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.NullPointerException: Could not find result attribute DescHead in target object. at Filter.DS_GetSuggestion (JavaAction : 'Execute OQL statement') Advanced stacktrace:    at com.mendix.modules.microflowengine.MicroflowUtil$.processException(MicroflowUtil.scala:73) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.NullPointerException: Could not find result attribute DescHead in target object.    at com.mendix.basis.actionmanagement.ActionManager.executeSync(ActionManager.scala:109) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.NullPointerException: Could not find result attribute DescHead in target object.    at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:23) Caused by: java.lang.NullPointerException: Could not find result attribute DescHead in target object.   I've verified that each entity, attribute, association, and name are spelled and created correctly. If anyone could explain the problem and how to fix it, I'd appreciate it. 
asked
3 answers
0

I would avoid Head as Alias give all columns an alias and improve the OQL like this (try editing it in a temporary data_set)

 

SELECT 
  Sug.NameSuggestion AS NameSuggestion,
  Sug.Status AS Status,
  head1.DescHead AS DescHead
FROM Core.Suggestion AS Sug
INNER JOIN Core.Suggestion_Department/ImportData.Department as head1

or if your headquarter is a specialisation of Department

SELECT 
  Sug.NameSuggestion AS NameSuggestion,
  Sug.Status AS Status,
  head1.DescHead AS DescHead
FROM Core.Suggestion AS Sug
INNER JOIN Core.Suggestion_Department/ImportData.HeadQuarter as head1

 

answered
0

Hi Raul,

 

From 10.19 onwards, you can create view entities. https://docs.mendix.com/refguide/view-entities/ Have a look here https://www.youtube.com/watch?v=8h6WDpJZME4 for a brief overview.

 

What I like about it is that the OQL editor proves validity of the statement.

 

So if you are on 10.19 or above, have a look.

 

Go Make It 

answered
0

The way to write a join is by first stating one entity, then the name of the association, and then the associated entity, e.g., Shop.OrderLine/Shop.OrderLine_Order/Shop.Order

 

See example here with a view entity:

 

image.png

answered