function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Aron SchorAron Schor 

Help with a SOQL query (Sales order / order detail relationship)

I am having problems with this query, which I believe to be similar to the second basic one.

SELECT Order_Value__C
    ( SELECT Warehouse__C FROM Order_Detail__C )    FROM Order_header__c

I assume the relationship here is the same?
SELECT Id, Name, Industry, AnnualRevenue,
    ( SELECT Name, Email, BirthDate FROM Contacts )    FROM Account

It says

MALFORMED_QUERY:
( SELECT Warehouse__C FROM Order_Detail__C
^ ERROR at Row:2:Column:6
unexpected token: 'SELECT'

In our System API Name Order_Details__c show
Field Label Order Header
API NAME Order_Header__c
Data Type Master-Detail(Sales Order) 

I assume the relationship would be the same and it would work?

Individually both of these work
This works SELECT Order_Value__C FROM Order_header__c
This works SELECT Warehouse__C FROM ORDER_Detail__C
Best Answer chosen by Aron Schor
Frédéric TrébuchetFrédéric Trébuchet
That's an other question!
Your query retrieves all records from Order_header__c where ORDER_Date__C between 2 dates and Warehouse__C FROM Order_Detail__r if one or more Order_Detail__r are joined with Order_header__c.
I suppose you want select Order_header__c only when Order_Detail__r have Warehouse__C = '57'.

This one should give you the result you want (I hope so): 
SELECT customer_Name__C, customer_Number__C, Purchase_order__C, NAME, Order_Value__C, Order_Date__c
   FROM Order_header__c
   WHERE ORDER_Date__C > 2014-12-02 AND Order_Date__C < 2014-12-10
   AND id IN (select Order_header__c FROM Order_Detail__c WHERE Warehouse__C = '57')
Fred

All Answers

Frédéric TrébuchetFrédéric Trébuchet
Hi,

Missing "," after Order_Value__C
Try this 
SELECT Order_Value__C,
    ( SELECT Warehouse__C FROM Order_Detail__C )    FROM Order_header__c
Hope this helps,
Fred
 
janardhan mjanardhan m
Try this,
SELECT Order_Value__C,
    ( SELECT Warehouse__C FROM Order_Detail__C )    FROM Order_header__c
Aron SchorAron Schor

Ok, its semi working!  However, not all the results are warehouse 57.  Any idea why?

SELECT customer_Name__C, customer_Number__C, Purchase_order__C, NAME, Order_Value__C, Order_Date__c,
    ( SELECT Warehouse__C FROM Order_Detail__r WHERE Warehouse__C = '57' )    FROM Order_header__c
WHERE ORDER_Date__C > 2014-12-02 AND Order_Date__C < 2014-12-10 

Here is a partial screen shot and some are for a different warehouse.
 

Thanks.

User-added image
Frédéric TrébuchetFrédéric Trébuchet
That's an other question!
Your query retrieves all records from Order_header__c where ORDER_Date__C between 2 dates and Warehouse__C FROM Order_Detail__r if one or more Order_Detail__r are joined with Order_header__c.
I suppose you want select Order_header__c only when Order_Detail__r have Warehouse__C = '57'.

This one should give you the result you want (I hope so): 
SELECT customer_Name__C, customer_Number__C, Purchase_order__C, NAME, Order_Value__C, Order_Date__c
   FROM Order_header__c
   WHERE ORDER_Date__C > 2014-12-02 AND Order_Date__C < 2014-12-10
   AND id IN (select Order_header__c FROM Order_Detail__c WHERE Warehouse__C = '57')
Fred
This was selected as the best answer
Aron SchorAron Schor
Thanks Fred! Works perfectly!