Apex Code Development You need to sign in to do that
Don't have an account?
unidha 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?
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
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
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.
But my requirement is not retrieve parameter, but compare value between two column something like below:
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.
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'
I will choose the second solution , using the formula field.
Thanks again.
******************************
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