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
admin Affinityadmin Affinity 

Subquery Complex

Hi I'm trying to create a client list with, the most recent order, associated but I don't know do the condition between those entities. I need to reference in the second query the first table.
  
SELECT 	...,
		tb01.Account.Id,
		...,
		(
			SELECT tb02.DAT_Real_Delivery_Date__c 
			  FROM Order tb02
			 WHERE tb02.AccountId 					= tb01.Account.Id
			   AND tb02.CHK_Collection__c			= false 
			   AND tb02.Status 						= 'Sent' 
			   AND tb02.DAT_Real_Delivery_Date__c	> TODAY
		  ORDER BY tb02.DAT_Real_Delivery_Date__c DESC NULLS LAST 
			 LIMIT 1
		) DAT_Real_Delivery_Date
FROM 	Event tb01
WHERE 	Type IN ('Off Route','Call') 
AND 	tb01.ActivityDate <= today 
AND    (tb01.Activity_Status__c != 'Completed' AND tb01.Activity_Status__c != 'Cancelled') 
AND    (tb01.OwnerId =: userId OR tb01.OwnerId =: otherUserId) 
AND 	tb01.IsRecurrence = false 
AND 	tb01.AccountId != null 
ORDER BY tb01.Account.PCK_Customer_Status__c ASC, tb01.ActivityDateTime ASC 
LIMIT 200
How can I do this assignment?
tb02.AccountId = tb01.Account.Id

thanks all
regards
 
Nayana KNayana K
We can't do so in query. It will always be like tb02.AccountId = some static value or variable.

You can go for formula field which returns Boolean on Order object, if possible:
Say a formula field EqualAccountId__c = (AccountId == Event.AccountId)

Then alter the query like 
SELECT 	...,
		tb01.Account.Id,
		...,
		(
			SELECT tb02.DAT_Real_Delivery_Date__c 
			  FROM Orders tb02
			 WHERE EqualAccount__c = TRUE
			   AND tb02.CHK_Collection__c			= false 
			   AND tb02.Status 						= 'Sent' 
			   AND tb02.DAT_Real_Delivery_Date__c	> TODAY
		  ORDER BY tb02.DAT_Real_Delivery_Date__c DESC NULLS LAST 
			 LIMIT 1
		) DAT_Real_Delivery_Date
FROM 	Event tb01
WHERE 	Type IN ('Off Route','Call') 
AND 	tb01.ActivityDate <= today 
AND    (tb01.Activity_Status__c != 'Completed' AND tb01.Activity_Status__c != 'Cancelled') 
AND    (tb01.OwnerId =: userId OR tb01.OwnerId =: otherUserId) 
AND 	tb01.IsRecurrence = false 
AND 	tb01.AccountId != null 
ORDER BY tb01.Account.PCK_Customer_Status__c ASC, tb01.ActivityDateTime ASC 
LIMIT 200


 
admin Affinityadmin Affinity
This field can not be created. From the Entity Order there is no access to the Entity Event. Also the cardinality is not 1 to 1
Cardinality is n n n
It is not a direct relationship but through the entity account