+ Start a Discussion

Error trying to run SOQL relationship query with external objects

I am getting the following error when I try to run a SOQL relationship query involving external objects: UNKNOWN ERROR: UNKNOWN_EXCEPTION: An unexpected error occurred. Please include this ErrorId if you contact support: 1553848083-42327 (-438141556). I get this same error using the Force.com IDE, Workbench or Developer Console.

Are releationship queries not allowed for external objects? The docs said that up to 4 joins are allowed.

My query looks like this:

Select s.sarahasnolimits__LastName__c, s.sarahasnolimits__FirstName__c, (Select sarahasnolimits__InvDate__c, sarahasnolimits__Status__c, sarahasnolimits__TotalPrice__c From sarahasnolimits__Invoices__r) From sarahasnolimits__Customers__x s

Can you please make sure there is lookup relationship between sarahasnolimits__Invoices__c(child) and sarahasnolimits__Customers__x(parent)  objects?
Does the error still occur if you remove the inner query? Also, is there any formula fields in your query? Could you remove that field and see whether it works fine?
I should have mentioned in the original post that the error only occurs when I include the inner query. Also, the query does not contain any formula fields. 

I have created an external lookup between Invoices (child)  and Customers (parent). That was necessary or else I would have not even been able to execute the query. 

 ​I do NOT think it is an issue with external objects, but perhaps with relationships AND external objects. Thanks for the suggestions.

I also tried it with anonymous code in Dev Console and trying to run the query to return a list and the request seems to be timing out. No error is returned in that case.

Finally, if I click the Use Tooling API checkbox in Dev Console before I run the query, I get an error saying sobject type is not supported, which tells me that possibly this is just not a supported functionality yet since external objects are so new. But I wanted to confirm that because I am writing an article for DeveloperForce about Lightning Connect and did not want to state that something was not supported if there was another explanation.
I also tried to execute the following anyonymous code in the Force.com IDE and I got the following error:
An internal error occurred during: "Execute-Anonymous".

List<sarahasnolimits__Customers__x> customers = [Select s.sarahasnolimits__LastName__c, s.sarahasnolimits__FirstName__c, (Select sarahasnolimits__CustId__c, sarahasnolimits__InvDate__c, sarahasnolimits__Status__c, sarahasnolimits__TotalPrice__c From sarahasnolimits__Invoices__r) From sarahasnolimits__Customers__x s];
OK, so I finally figured out what the problem was. It had to do with how I defined the external lookup relationship. I went to the external object definition page and clicked New to create a new field and then define the relationship. In order to avoid the error, you will need to instead edit one of the existing SYNCED fields and specifically Change the Field Type so that it is of the External Lookup Relationship type and that it points to the correct external table object. Then, you can perform your SOQL query and get no errors.