Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SoundarSoundar 

Reduce Queries and Write all functionality in single query by using Map

Dear friends,

I like to reduce this Query size (four --> single ) . Kindly advise me once how it's possible by using Map
  
public class OppUsdUpdate {
    
    public static void usdUpdate(Id Opportunity, String str){
    
        List<Opportunity> oppList = New List<Opportunity>();
        
                          
        List<Opportunity> oppQry = [Select id, name, USD_Amount1__c,Amount, Currency__c from  Opportunity where Id =:Opportunity];
         
        CurrencyType__c cTypeInr  =[Select id,name,Conversion_Rate__c, ISO_Code__c from CurrencyType__c where 
                                    ISO_Code__c = 'INR'  Limit 1];
          
        CurrencyType__c cTypeUsd  =[Select id,name,Conversion_Rate__c, ISO_Code__c from CurrencyType__c where ISO_Code__c = 'USD'   Limit 1];
          
        CurrencyType__c cTypeEur  =[Select id,name,Conversion_Rate__c, ISO_Code__c from CurrencyType__c where ISO_Code__c = 'EUR' Limit 1];
          
        CurrencyType__c cTypeAud  =[Select id,name,Conversion_Rate__c, ISO_Code__c from CurrencyType__c where ISO_Code__c = 'AUD' Limit 1];
          
        /*INDIAN RUPEE*/
        for(Opportunity opp : OppQry){
        
            opportunity op = new Opportunity(Id = Opp.Id);
            
            if(opp.Currency__c == 'INR'){
                op.usd_amount1__c = opp.Amount * cTypeInr.Conversion_Rate__c;
            } 
            if(opp.Currency__c  == 'USD'){
                op.usd_amount1__c = opp.Amount * cTypeUsd.Conversion_Rate__c;
            //oppList.add(op);
            }  
            if(opp.Currency__c  == 'EURO'){
                op.usd_amount1__c = opp.Amount * cTypeEur.Conversion_Rate__c;
            } 
            if(opp.Currency__c  == 'AUD'){ 
                op.usd_amount1__c = opp.Amount * cTypeAUD.Conversion_Rate__c;            
            }
            oppList.add(op);
        }
        
        if(oppList.size() > 0){
            update opplist;
        }
        
   }

}

Regards,

Soundar Raj
+91-741842418​
Best Answer chosen by Soundar
Shruti SShruti S
Here is the optimized code -  
public class OppUsdUpdate {
    
    public static void usdUpdate( Id oppId, String str ){
        List<CurrencyType__c > currencyTypes = List<CurrencyType__c >();

        /**
         * Collected all the Currency Type
         * Records into a List of CurrencyType__c
         */
        currencyTypes = [
            SELECT  Id  
                    ,Name
                    ,Conversion_Rate__c
                    ,ISO_Code__c 
            FROM    CurrencyType__c
        ];

        Map<String,Decimal> currencyRates = new Map<String,Decimal>();

        /**
         * Iterate the CurrencyType__c List and
         * generate a Map with the ISO_Code__c
         * values as the key and its Conversion_Rate__c
         * as the value.
         */
        for( CurrencyType__c currency : currencyTypes ) {
            currencyRates.put( currency.ISO_Code__c, currency.Conversion_Rate__c );
        }

        List<Opportunity> oppList = new List<Opportunity>();

        /**
         * Query all the Opportunity
         * records.
         */
        oppList = [
            SELECT  Id
                    ,Name
                    ,Amount
                    ,Currency__c
                    ,USD_Amount1__c
            FROM    Opportunity
            WHERE   Id = :oppId
        ];

        /**
         * Iterate the Opportunities and
         * update its USD_Amount1__c field
         * as its Amount * the Conversion_Rate__c.
         * Its respective Conversion_Rate__c can
         * be got from the map by giving the
         * Opportunity's Currency__c as the key
         * which is nothing but the ISO_Code__c
         * value.
         */
        for( Opportunity opp : oppList ) {
            opp.USD_Amount1__c = opp.Amount * currencyRates.get( opp.Currency__c );
        }

        UPDATE oppList;
    }
}
Feel free to ask if you have any doubts.

All Answers

Hemant_JainHemant_Jain
See if below code helps:  
Map<String, CurrencyType__c> cTypeMap = new Map<String, CurrencyType__c>();
List<CurrencyType__c> cTypeList =[Select id,name,Conversion_Rate__c, ISO_Code__c from CurrencyType__c where  ISO_Code__c IN ('INR', 'USD','EUR', 'AUD')  Limit 1];
for(CurrencyType__c ctypObj: cTypeList)
{
	cTypeMap.put(ctypObj.ISO_Code__c,ctypObj);
}

//Then you can get the value as given below:
cTypeMap.get('INR').Conversion_Rate__c; //To get INR Rate

 
Hemant_JainHemant_Jain
Correction: Remove LIMIT 1 from the query
Shruti SShruti S
Here is the optimized code -  
public class OppUsdUpdate {
    
    public static void usdUpdate( Id oppId, String str ){
        List<CurrencyType__c > currencyTypes = List<CurrencyType__c >();

        /**
         * Collected all the Currency Type
         * Records into a List of CurrencyType__c
         */
        currencyTypes = [
            SELECT  Id  
                    ,Name
                    ,Conversion_Rate__c
                    ,ISO_Code__c 
            FROM    CurrencyType__c
        ];

        Map<String,Decimal> currencyRates = new Map<String,Decimal>();

        /**
         * Iterate the CurrencyType__c List and
         * generate a Map with the ISO_Code__c
         * values as the key and its Conversion_Rate__c
         * as the value.
         */
        for( CurrencyType__c currency : currencyTypes ) {
            currencyRates.put( currency.ISO_Code__c, currency.Conversion_Rate__c );
        }

        List<Opportunity> oppList = new List<Opportunity>();

        /**
         * Query all the Opportunity
         * records.
         */
        oppList = [
            SELECT  Id
                    ,Name
                    ,Amount
                    ,Currency__c
                    ,USD_Amount1__c
            FROM    Opportunity
            WHERE   Id = :oppId
        ];

        /**
         * Iterate the Opportunities and
         * update its USD_Amount1__c field
         * as its Amount * the Conversion_Rate__c.
         * Its respective Conversion_Rate__c can
         * be got from the map by giving the
         * Opportunity's Currency__c as the key
         * which is nothing but the ISO_Code__c
         * value.
         */
        for( Opportunity opp : oppList ) {
            opp.USD_Amount1__c = opp.Amount * currencyRates.get( opp.Currency__c );
        }

        UPDATE oppList;
    }
}
Feel free to ask if you have any doubts.
This was selected as the best answer
SoundarSoundar
Hi HemantJain0206,

Thanks For your quick response.. I had make some changes based on Shruthi Optimized Code.

Really Hearty Thanks to spent your golden time for me.

Regards,
Soundar Raj 
+91- 7418425418
SoundarSoundar
Hi ,

Thanks for Optimized my Code as well. I have make few changes as per my knowledge (I Think few changes only).

1. It's a utilty class , This class is refered in one  trigger.
2. Update Processing in trigger.

Class
  
public class OppUsdUpdate 
{
    public static String usdUpdate(String CurCode)
    {
        String retValue;
        Map<String,Decimal> otrAmnt = New Map<String,Decimal>();
        CurrencyType__c cTypeInr = [Select id,name,Conversion_Rate__c, ISO_Code__c from CurrencyType__c where ISO_Code__c =:CurCode];
        retValue = string.valueof(cTypeInr.Conversion_Rate__c);
        return retValue ;
    }  
}

Trigger
  
trigger amountConvertion on Opportunity (after insert) {
    
    
        Set<String> oppSet = new Set<String>();
        List<Opportunity> opList = New List<Opportunity>();
        
        for(Opportunity op : Trigger.new){
            
            oppSet.add(op.Id);
        }
        
    
     List<Opportunity> oppQry = [Select id, name,USD_Amount1__c,Amount, Currency__c from  Opportunity where Id IN:oppSet];
        System.debug(oppQry);
       
                          
        
          
        /*INDIAN RUPEE*/
         for(Opportunity opp : OppQry)
         {
           
                Opportunity iopp = new Opportunity();
                iopp.Id = opp.Id;
                System.debug(iopp.Id);
                String str = OppUsdUpdate.usdUpdate(opp.Currency__c);  // From Class
                iopp.USD_Amount1__c = Decimal.valueof(str) * opp.Amount;
                System.debug(iopp.USD_Amount1__c);
                opList.add(iopp);
                System.debug(opList);
            
           
         } 
         try
           {
         update opList;
         
         }
         catch(Exception e)
    {
        System.debug(e.getMessage() + e.getLineNumber());
    }
         
         
}


Regards,

Soundar Raj
+91-7418425418