[OQL] Join across two more tables.

0
Hello there.  I'm looking to create an OQL query that plumbs data from two more linked tables, but I cannot seem to get the syntax correct. I've tried something like this: SELECT DISTINCT COUNT(*) AS IntValue FROM Customer.CustomerAccount c INNER JOIN SalesOrderProcessing.SalesOrder_CustomerAccount/SalesOrderProcessing.SalesOrder AS s INNER JOIN SalesOrderProcessing.SalesOrder_Depot/Business.Depot as d INNER JOIN SalesOrderProcessing.SalesOrder_SalesRep/Business.SalesRep as r WHERE s.OrderDate >= $LowerDate and s.OrderDate <= $UpperDate AND s.SalesOrderStatus IN ('Allocated', 'Dispatch') AND d.DepotName IN ('Bournemouth','Dover') AND r.SalesRepShortName in ('Charlie Angel','FWalsh') but I get an error message back: Caused by: com.mendix.connectionbus.ConnectionBusRuntimeException: There isn't a meta association with name 'SalesOrderProcessing.SalesOrder_Depot' of meta object 'Customer.CustomerAccount' I think I need to make a link between the sales order and the customer account, but I'm not sure how that's done in OQL. Can anyone help with that?   Thanks.  
asked
3 answers
2

Here's a really great way to validate your OQL query and make maximum use of the autocomplete features offered by Mendix: create a Data set.

Judging from your initial OQL query, am I correct in stating that you do have an association between CustomerAccount and SalesOrder? There's two things I noticed:

  1. As corrected by Tim in his edited query, you need to define your variables with AS
  2. Your inner join needs to originate from one of the available variables.

This means that for example 

FROM Customer.CustomerAccount c 

becomes

FROM Customer.CustomerAccount AS c

Another example is that this

INNER JOIN SalesOrderProcessing.SalesOrder_CustomerAccount/SalesOrderProcessing.SalesOrder AS s

becomes

INNER JOIN c/SalesOrderProcessing.SalesOrder_CustomerAccount/SalesOrderProcessing.SalesOrder AS s

 

answered
1

Thanks   very much for that.

But looking at the answer, would I be correct in assuming that there is no way to jump from linked table to linked table by comparing fields, as you can in regular SQL?

 

answered
0

This message arises because SalesOrder_Depot is not an association in entity Customer. Try my untested first guess:

SELECT DISTINCT COUNT(*) AS IntValue FROM SalesOrder AS s
 INNER JOIN SalesOrderProcessing.SalesOrder_Depot/Business.Depot as d 
 INNER JOIN SalesOrderProcessing.SalesOrder_SalesRep/Business.SalesRep as r
 INNER JOIN SalesOrderProcessing.SalesOrder_Customer/Customer.CustomerAccount as c
 WHERE s.OrderDate >= $LowerDate and s.OrderDate <= $UpperDate
 AND s.SalesOrderStatus IN ('Allocated', 'Dispatch')
 AND d.DepotName IN ('Bournemouth','Dover')
 AND r.SalesRepShortName in ('Charlie Angel','FWalsh') 

answered