Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sudhirn@merunetworks.comsudhirn@merunetworks.com 

Condition based SOQL

Hi, 
  
 I wrote below code to test conditional SOQL based on the value we have 

  In below code when country != null it must return a soql value when zip != null it must return another soql value but its not working and going to a exception clause can you please suggest me what is the mistake in the code. 
  
Try
{       
   
lead ld = [select id,postalcode,state,country from lead where id = '00Q180000029Ujz' limit 1];

      system.debug('Lead ID:' + ld.id);
      system.debug('Postal Code:' + ld.postalcode);
      system.debug('State:' +ld.state);
      system.debug('Country:' + ld.country);
    
    Integer intzip;
    intzip=Integer.valueof(ld.postalcode);
    String lstate;
    lstate = ld.state;
    String lcountry;
    lcountry = ld.country;
    
    system.debug('Updated Zip:' + intzip);
    system.debug('State:' + lstate);
    system.debug('Country:' + lcountry);
    
     if ( lcountry != null )
     {
         Territory_Lookup__c tl = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c
                              where  Country__c = :lcountry];   
     system.debug('Theater1 :' + tl.Theater__c);
     system.debug('Region1 :' + tl.Region__c);
     system.debug('Area1 :' + tl.Area__c);
     system.debug('User1 :' + tl.User__c);
     }  
    
    if ( intzip != null )
          
     {
        Territory_Lookup__c tl = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c
                              where Zip_Start__c <= :intzip and Zip_End__c >= :intzip];     
     system.debug('Theater2 :' + tl.Theater__c);
     system.debug('Region2 :' + tl.Region__c);
     system.debug('Area2 :' + tl.Area__c);
     system.debug('User2 :' + tl.User__c);    
         }

    
}     
Catch (Exception e)
{
 system.debug('Lookup no found'); 
    }


Thanks
Sudhir
Best Answer chosen by sudhirn@merunetworks.com
RohRoh

Hello Sudhir,
Try to modify the code to below and try ,
  
Try { 

lead ld = [select id,postalcode,state,country from lead where id = '00Q180000029Ujz' limit 1]; 

if ( ld.country != null && ld.country != '') 
{
 List<Territory_Lookup__c> tl = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c where Country__c = :ld.country];  
 if(tl != null && !tl.isempty())
 system.debug('New tl' + t1[0].id); 
} 
if ( ld.intzip != null && ld.intzip != '' ) 
{
 List<Territory_Lookup__c> t2 = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c where (Zip_Start__c <= :ld.intzip AND Zip_End__c >= :ld.intzip)];   if(t2 != null && !t2.isempty())
system.debug('New t2' + t2[0].intzip); 
} 
} 
Catch (Exception e) 
{ system.debug('Lookup no found'); }


PLEASE SELECT THIS AS THE RIGHT ANSWER, IF YOU LIKE IT.

Thanks,
Rohit Alladi

All Answers

RohRoh

Hello Sudhir,
Try to modify the code to below and try ,
  
Try { 

lead ld = [select id,postalcode,state,country from lead where id = '00Q180000029Ujz' limit 1]; 

if ( ld.country != null && ld.country != '') 
{
 List<Territory_Lookup__c> tl = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c where Country__c = :ld.country];  
 if(tl != null && !tl.isempty())
 system.debug('New tl' + t1[0].id); 
} 
if ( ld.intzip != null && ld.intzip != '' ) 
{
 List<Territory_Lookup__c> t2 = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c where (Zip_Start__c <= :ld.intzip AND Zip_End__c >= :ld.intzip)];   if(t2 != null && !t2.isempty())
system.debug('New t2' + t2[0].intzip); 
} 
} 
Catch (Exception e) 
{ system.debug('Lookup no found'); }


PLEASE SELECT THIS AS THE RIGHT ANSWER, IF YOU LIKE IT.

Thanks,
Rohit Alladi
This was selected as the best answer
Arun KumarArun Kumar
Hi Sudhirn,

Please try below code   
Try
{       
   
lead ld = [select id,postalcode,state,country from lead where id = '00Q180000029Ujz' limit 1];

      system.debug('Lead ID:' + ld.id);
      system.debug('Postal Code:' + ld.postalcode);
      system.debug('State:' +ld.state);
      system.debug('Country:' + ld.country);
    
    Integer intzip;
    intzip=Integer.valueof(ld.postalcode);
    String lstate;
    lstate = ld.state;
    Id lcountry;
    lcountry = ld.country;
    
    system.debug('Updated Zip:' + intzip);
    system.debug('State:' + lstate);
    system.debug('Country:' + lcountry);
    
     if ( lcountry != null )
     {
         Territory_Lookup__c tl = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c
                              where  Country__c = :lcountry];
		if(tl.Id != null)
		 system.debug('Theater1 :' + tl.Theater__c);
		 system.debug('Region1 :' + tl.Region__c);
		 system.debug('Area1 :' + tl.Area__c);
		 system.debug('User1 :' + tl.User__c);
		}     
     }  
    
    if ( intzip != null )
          
     {
        Territory_Lookup__c tl = [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c
                              where Zip_Start__c <= :intzip and Zip_End__c >= :intzip];
			if(tl != null){
			 system.debug('Theater2 :' + tl.Theater__c);
			 system.debug('Region2 :' + tl.Region__c);
			 system.debug('Area2 :' + tl.Area__c);
			 system.debug('User2 :' + tl.User__c);  
			}      
         }    
}     
Catch (Exception e)
{
 system.debug('Lookup no found' + e); 
}

Please let me know if the helps.

As a common practice, if your question is answered, please choose 1 best answer.
Additionaly you can give every answer a thumb up if that answer is helpful to you.

Thanks,
Arun
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thanks for your reply Roh and Arun I modified code a bit and applied that on a trigger problem am facing here is when user updates the lead or zip its not updating for first time. when user again tries to edit and save it is updating the right value please suggest me how to fix this issue

  
trigger territory_lookup on Lead (Before Insert, Before Update) 
{
 List<Territory_Lookup__c> territoryLookupList = null;  
 List<Integer> gzip = new List<Integer>(); // we need a list so that we can sort it.
 Set<String> gstate = new Set<String>();
 Set<String> gcountry = new Set<String>(); 
 
 /*for(Lead l :   Trigger.new){
  gstate.add(l.state);
  gcountry.add(l.country);
  gzip.add(Integer.valueof(l.postalcode));
 }*/
 
 lead ld = [select id,postalcode,state,country from lead where id = :trigger.newMap.keySet() limit 1];
 
   Integer intzip;
     if ( ld.postalcode != null ) 
     {
      intzip=Integer.valueof(ld.postalcode);
      }
 
   if ( ld.country != null && ld.country != '' )
   {
  territoryLookupList =  [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c
                           where  Country__c = :ld.country limit 1];   
   }

if ( ld.postalcode != null && ld.postalcode != '' ) 
{
 territoryLookupList =  [select Theater__c,Region__c,Area__c,User__c FROM Territory_Lookup__c
                           where  Zip_Start__c <= :intzip and Zip_End__c >=:intzip limit 1]; 

}
  for(lead uld :   Trigger.new){
 
   Territory_Lookup__c tl =   getTerritoryLookup(uld);
   if(tl !=null){
     uld.Territory_Area__c = tl.Area__c;
     uld.Territory_Region__c = tl.Region__c;
     uld.Territory_Theater__c = tl.Theater__c;
    }
   }
  
   
 public Territory_Lookup__c getTerritoryLookup(Lead lead){
  if(territoryLookupList == null)
    return null;
  Territory_Lookup__c temp = null;
  for(Territory_Lookup__c territoryLookup : territoryLookupList){
    //Integer zip = Integer.valueof(lead.postalcode);
   
      temp =  territoryLookup;
      break;
    
  }
  return temp;
 }
   
}


Thanks

Sudhir