+ Start a Discussion
Anthony ScatamacchiaAnthony Scatamacchia 

Can someone help me translate this boolean query??

I have some comfort with SQL, but not enough that I feel comfortable making changes to some of our internal call cycle logic without clarifying what the current query means.  My main question/confusion is around the "from FROM Account_Territory__c a" statement.  When I look at that object in my metadata, many of the fields used in the query below are not housed there (LCDFI_c, a.Customer__r.Revenue_Gain_or_Decline__c, and a few others), but they are all located within the overarching Accounts object.  If any of the metadata tables within the account object are referenced, can I "call on" any of the fields in the account object?  If not, how do I determine which metadata object to set the WHERE statement to?  If so, why would I not just reference the Account object to begin with?


One of my projects is around cleaning up these queries, and since I am new to this part of SF I wanted to make sure I am going in the right direction.  Thanks for any and all help!



SELECT Id, a.Customer__r.Id, a.Customer__r.Type, a.Customer__r.LCDFI__c, a.Customer__r.LCD_All__c, Primary_Team_Member__c 
FROM Account_Territory__c a
WHERE $existingAccountIdCriteria $customerTypeCriteria
a.Territory_Custom__c = $territoryId and 
a.Remove_From_Call_Cycle__c = false and a.Is_Marked_for_Delete__c = false and 
a.Customer__r.Sales_Ok_To_Call__c !='No' and ((a.Customer__r.$LCDField = null) OR
(a.Customer__r.$LCDField < LAST_N_DAYS:$DBC)) and 
a.Customer__r.Revenue_Gain_or_Decline__c < 0 
ORDER BY a.Customer__r.Revenue_Gain_or_Decline__c desc, a.Customer__r.$LCDField NULLS LAST 
LIMIT $capacity

Best Answer chosen by Anthony Scatamacchia



First of all take a look at this: http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_relationships.htm


"FROM Account_Territory__c a" a is an alias, it is used to reference this object in other parts of the query

Fields that end with __c are custom fields. They can be of many types.

Objects whose api name ends in __c are custom objects. 

Account_Territory__c is a custom object. It has a custom field(relationship) to the standard Account through Customer__c, which can be a Lookup or a Master-Detail. Changing the __c to __r will permit you to traverse the lookup relationship and access the fields of the parent from the child level. For example, a.Customer__r.Type will reference the Type field from the Account parent of the Account Territory. 


The WHERE clause uses fields from Account_Territory__c level so the query is set okay with  "FROM Account_Territory__c"


What exactly do you want to do? I don't understand the exact purpose of modifying the query