+ Start a Discussion
unidhaunidha 

SOQL To retrieve data with matched value from two columns from the same object

Hi,

 

I am not sure my Subject is correctly write or not.But I faced issue on something like this,

 

Example I have one object named Employee with contains attribute such  InternalID ,ExternalID, name , age

 

I need to retrieve the data where InternalId = ExternalID something like below

 

select Name, Age__c  from Employee__c where InternalID__c = ExternalID__c 

 

 

I knew in Oracle we can do something like this, but I m not sure in SFDC can we do that?Usually if there requirement to do that, what is the best way to do it?

Best Answer chosen by Admin (Salesforce Developers) 
Michael_TorchedloMichael_Torchedlo

This syntax will not work if you are trying to compare two fields in the same record:

 

select First_Name__c , Last_Name__c from Candidate__c where First_Name__c =Last_Name__c

 

Assuming you can form a query small enough for the governor limits, you could use a loop, like this.  The for loop goes through all the candidates in your query, but it does not store them all in your collection; only the ones that do have the two columns match.

 

List<Candidate__c> QResults = new List<Candidate__c>();

for(Candidate__c c: [SELECT id,first_name__c,last_name__c FROM Candidate__c LIMIT 30000]){

   if(c.first_name__c == c.last_name__c){

      QResults.add(c);

   }

}

 

Another option you might consider is to create a custom formula field on your Candidate__c object.  The field does not need to be seen on the page layout, or visible to users unless you want to have it in reports as well.  The format can be text and set the formula like IF( First_Name__c == Last_Name__c, 'TRUE', 'FALSE').  Then in your query, you can do

 

SELECT id,first_name__c,last_name__c, name_match_formula__c FROM Candidate__c WHERE name_match_formula__c = 'TRUE'

 

All Answers

Dorian - JLCDorian - JLC

Hi,

 

To acces data from SF database you have to use SOQL ( SQL simplify by SalesForce) in  a controller (apex class) ,

you have a well explain tutoriel right here : (page 38)

 

http://www.salesforce.com/us/developer/docs/apex_workbook/apex_workbook.pdf

 

Best Regards,

Dorian

unidhaunidha

Hi ,

 

Thanks for that. I went thru it already but it seem I could not find what I am looking for.

 

I google  but found out mostly query is similar like this, need to receive the parameter.

 

select First_Name__c , Last_Name__c from Candidate__c where First_Name__c = 'Joe' and Last_Name__c='Joe'

 But my requirement is not retrieve parameter, but compare value between two column something like below:

 

select First_Name__c , Last_Name__c from Candidate__c where First_Name__c =Last_Name__c 

 I only get example in internet for Date comparison but not for other datatype.I run the query above and it keep prompt me the error.

Unknown error parsing query.

The column that I want to compare is text datatype  with the same size .Just wondering I am doing wrong or actually it is limitation.

 

The only solution that I think of is to retrieve the data, put inside the list then write the logic to compare it , then get the match and put in the final list.Not sure if it will hit governor limit.

 

Thanks.

 

Michael_TorchedloMichael_Torchedlo

This syntax will not work if you are trying to compare two fields in the same record:

 

select First_Name__c , Last_Name__c from Candidate__c where First_Name__c =Last_Name__c

 

Assuming you can form a query small enough for the governor limits, you could use a loop, like this.  The for loop goes through all the candidates in your query, but it does not store them all in your collection; only the ones that do have the two columns match.

 

List<Candidate__c> QResults = new List<Candidate__c>();

for(Candidate__c c: [SELECT id,first_name__c,last_name__c FROM Candidate__c LIMIT 30000]){

   if(c.first_name__c == c.last_name__c){

      QResults.add(c);

   }

}

 

Another option you might consider is to create a custom formula field on your Candidate__c object.  The field does not need to be seen on the page layout, or visible to users unless you want to have it in reports as well.  The format can be text and set the formula like IF( First_Name__c == Last_Name__c, 'TRUE', 'FALSE').  Then in your query, you can do

 

SELECT id,first_name__c,last_name__c, name_match_formula__c FROM Candidate__c WHERE name_match_formula__c = 'TRUE'

 

This was selected as the best answer
unidhaunidha
Thanks, this is what I am looking for. At least I know it cannot be done.
I will choose the second solution , using the formula field.

Thanks again.
Gaurav Jain 67Gaurav Jain 67
having same issue.. I used the IF condition option : My scenario is (I need to Add a text 'Opportunity' to end of the Opportunity Name where Opportunity Name is equal to Account Name).  I am using following code,  but getting error on line 5 that "can compare a String to Account " .. The Field Name for Account NAme is Account and thus I am comparing that ..

******************************
LIST<opportunity> OppList = New LIST<opportunity>();
for(Opportunity O: [select id , name from Opportunity ])
{
    if(O.Name==O.Account){
O.Name=O.Name + 'Opportunity';
OppList.Add(O);
    }  
}
Update OppList;
********************************

Please help