+ Start a Discussion
AndreasMucAndreasMuc 

Dynamic query error ... only in live org

Hi,
I'm having trouble with the following code in the live system ... in the sandbox everthing works fine:

...
Map<String, Schema.SObjectField> M2;		
String queryLead = 'SELECT '; 
M2 = Schema.SObjectType.Lead.fields.getMap();

for(string fName : M2.keySet()){
   queryLead += fName+',';
}       

queryLead = queryLead.removeEnd(','); 
queryLead += ' FROM Lead WHERE Email IN :emails AND Email != null AND Id NOT IN :contactOrLeadIds';

List<Lead> leads = Database.query(queryLead);
The Database.query line produces an error saying that the field "address" could not be accessed and all the __c thing ... but its just in the live system ...
Any ideas?

Thanks!

goabhigogoabhigo
Are those fields created in production/live? If not, you need to deploy the fields first then the code.
Denis VakulishinDenis Vakulishin
Maybe cause is different Field-level security
AndreasMucAndreasMuc
Thanks for replying.
Address is a Lead standard field.
When I try to query it by the Query Editor it shows up with an error too ... but same query in the anonymous window everything works fine (in the sandbox!).
I think the field is after all a combination of the fields street, city etc. But this should not be the problem since it works in the Sandbox and I used this code in several orgs without problems ... 
AndreasMucAndreasMuc
FLS could be the problem ... I'll check it
Sagarika RoutSagarika Rout
I think , your sandbox class and production class are not in same version, As in each release , salesforce is modifying the schema, some times some data type get changed in higher versions. and if the respective class is in higher version , it will not suport previou datatypes. pls make production class in same api as it is in sandbox.

https://www.salesforce.com/us/developer/docs/api/Content/compound_fields_address.htm
AndreasMucAndreasMuc
The classes both have version 29. FLS shouldnt be the problem too.
But I just realized that I can now excecute the anonymous code in the live system ... which didnt work yesterday. Maybe I'll just give it one more try in the live org. I'll be confused if it really works now ;)
srlawr uksrlawr uk
FLS and object level securirty doesn't come into it from the execute anonymous window as this is run at a system level, so that shouldn't be a problem.

I would have thought the problem might be that - the way you are building up the query - you are trying to access the compound field "Address" directly, buyt (as you have stated) the address field is comprised of several fields itself (street. city etc.etc.) and that this is the problem with the database.query call - however, you say this works in Sandbox? That throws me off a bit... any problem like that suggests to me it is an "actual data" driven problem (assuming well kept sandboxes, complete releases etc.etc.) so I would look at differences between sandbox data and live data? (for example, you might not get this error if NO addresses are actually ever populated - ie a typical sandbox - but as soon as there are results to return, it starts kicking off?)

Either way, I am now spinning up your stuff in my Dev Org and I will let you know how/what I find think!
srlawr uksrlawr uk
Well ok. FYI, this (mini-variation) works fine in my Developer Edition:

Map<String, Schema.SObjectField> M2;		
String queryLead = 'SELECT '; 
M2 = Schema.SObjectType.Lead.fields.getMap();

for(string fName : M2.keySet()){
   queryLead += fName+',';
}       

queryLead = queryLead.removeEnd(','); 
queryLead += ' FROM Lead WHERE Email != null';

System.debug(queryLead);
List<Lead> leads = Database.query(queryLead);

System.debug(leads.size());
System.debug(leads.get(0));

It spits out the query:

SELECT postalcode,phone,jigsaw,street,leadsource,convertedopportunityid,isunreadbyowner,createdbyid,lastactivitydate,city,isconverted,description,isdeleted,longitude,industry,systemmodstamp,fax,status,converteddate,annualrevenue,primary__c,createddate,ownerid,lastvieweddate,jigsawcontactid,country,email,numberofemployees,company,latitude,productinterest__c,lastmodifiedbyid,photourl,state,lastname,lastmodifieddate,id,convertedaccountid,mobilephone,title,lastreferenceddate,emailbounceddate,name,numberoflocations__c,emailbouncedreason,website,masterrecordid,siccode__c,firstname,currentgenerators__c,convertedcontactid,address,rating,salutation FROM Lead WHERE Email != null

and then a result set of 22, and the first record is exactly a lead in my org. Therefore the code and concept must be intact. Scrap all my earlier speculation!
srlawr uksrlawr uk
Just some other brain dumps on this sort of query scenario (then I have to get back to work for a bit... sorry!)

Don't forget your limits on SOQL queries... they can often throw "suprious" looking messages not directly linked to the cause of the error (but the first point it died)
  • There is indeed a hard limit on the number of fields you can actually query - so check your lead object doesn't have all 500 odd custom fields, otherwise it might just be killing your query (sorry I can't remember this limit off the top of my head!)
  • Maximum query length 20,000 characters (and remember here, formula fields are expanded out into the query to their full length - including nesting so you can hit it faster than you think if you have big nested formulas)
  • On that note, you can only include a finite number of formula fields in a query, as you are doing a pseudo-select-all, make sure you don't have like 50 FF on your object.
  • You can only load 35 relationship references in a SOQL query too (but as this is a single object query you should be safe by default)
  • You can only load 50,000 rows (but of course, this is normally quite a clear error message)
  •  
Do you have any lead-based managed packages that might be causing security-style errors on query? and/or are there any code or configuration differences between your sandbox and live?
AndreasMucAndreasMuc
wow thanks for all these tips. I will have time later to work on that problem ... I will reply when I found the reason ;)
AndreasMucAndreasMuc
Unfortunately I hadnt the time to analyse this more in depth so I just replaced this dynamic query by a static one. But I will keep all the tips in mind for the next time ;) Thanks a lot !