Selecting ID from a joined table in OQL gives an error in a report pane

0
When I create a dataset with an OQL query that has an inner join with a table, I can put the ID column of the joined table in the OQL query without errors. Example: SELECT sbi.ID AS ShoppingBasketInventoryRefId , inv.ID AS InventoryRefId FROM Exchange.ShoppingBasketInventory AS sbi INNER JOIN sbi/Exchange.ShoppingBasketInventory_Inventory/Exchange.Inventory AS inv When a create a report pane based on the dataset and click the "Generate report" button, I get an error in the console. org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "inv" Is this a Mendix bug, or am I doing something wrong here? I can select any other column from the "inv" table just fine. Any help or tips are appreciated! Update 8/MAR/2016: The first proposed solution does not work, so any other tips are still appreciated!
asked
1 answers
5

Theo,

You need to include the table that you are aliassing as inv to the from clause and then JOIN the two. For example something like this will work:

select sbi.ID as a, vbi.ID as b
FROM MyFirstModule.Entity as sbi,
MyFirstModule.Entity_2 as vbi
INNER JOIN sbi/MyFirstModule.Entity_2_Entity/MyFirstModule.Entity_2

With as simple domain model of entity and entity2 in a 1:* relationship, this will deliver duplicates of course, but no error in the report pane

Edit: As stated the query above will deliver duplicates. When you include a second attribute in the query the statement can be performed as required. See:

select sbi.ID as a, vbi.Attribute as c, vbi.ID as b
FROM MyFirstModule.Entity as sbi
INNER JOIN sbi/MyFirstModule.Entity_2_Entity/MyFirstModule.Entity_2 as vbi

This will provide the correct results. Without the attribute selection the interpretation of the OQL by the server is not correct and I would suggest filing a support ticket for this. In the meantime just add an attribute other than the ID to retrieve the correct results.

answered