function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ramu Veligeti 5Ramu Veligeti 5 

SOQL query with datetime failing in Winter 16

When trying to execute the below SOQL query in Anonymous window under developer console, the query is failing with some Oracle exceptions. Based on the error, I can understand that this has to do something with datetime fields, so when I remove Call_Back_Time__c from the where clause of the query it executes fine. And the same SOQL executes absolutely fine in Summer 15 sandbox org.

Query:
List<Work_Item__c> wi = [Select Id, Priority__c, Response__r.Contact__c, Response__r.Contact__r.Assigned__c, Response__c, Response__r.Unit_of_Interest__c, Response__r.Unit_of_Interest__r.Assigned__c from Work_Item__c where Status__c = 'New' and Delivery_Mode__c = 'Call' and Response__r.RecordType.Name = 'Business Response' and Response__r.Contact__r.Assigned__c = false and Response__r.Unit_of_Interest__c != NULL and Response__r.Unit_of_Interest__r.Assigned__c = false  and Call_Back_Time__c  != NULL and Call_Back_Time__c <= :system.now() and ownerId = '005q0000001JFyJAAW' order by Call_Back_Time__c limit 1];

Error:
System.UnexpectedException: common.exception.SfdcSqlException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

select /*Apex.Class.WorkItemQueue.fetchWorkItem: line 26*/ *
from (select "Id",
"Priority__c",
"Response__c"
from (select /*+ ordered index(t iecustom_entity_data_owner) use_nl(t_c41) use_nl(t_c41_6) use_nl(t_c41_c9) use_nl(t_c41_c5) use_nl(cft_c41_c5) index(cft_c41_c5 PKCONTACT_CFDATA) */
t.deleted "IsDeleted_gen_1",
t_c41.deleted "IsDeleted_gen_2",
t_c41_c5.deleted "IsDeleted_gen_3",
t_c41_c9.deleted "IsDeleted_gen_4",
t.val7 "Status__c",
t.val48 "Delivery_Mode__c",
t_c41_6.name "t_c41_6_Name",
cft_c41_c5.val59 "t_c41_c5_Assigned__c",
t_c41.val9 "t_c41_Unit_of_Interest__c",
t_c41_c9.val156 "t_c41_c9_Assigned__c",
TO_DATE(t.val58) "Call_Back_Time__c",
t.owner "OwnerId",
t.custom_entity_data_id "Id",
TO_NUMBER(t.val4) "Priority__c",
t.val41 "Response__c"
from core.custom_entity_data t,
core.custom_entity_data t_c41,
core.record_type t_c41_6,
core.custom_entity_data t_c41_c9,
sales.contact t_c41_c5,
sales.contact_cfdata cft_c41_c5
where (t.val41 = t_c41.custom_entity_data_id)
and (t_c41.record_type_id = t_c41_6.record_type_id)
and (t_c41.val9 = t_c41_c9.custom_entity_data_id(+))
and (t_c41.val5 = t_c41_c5.contact_id(+))
and (t_c41_c5.contact_id = cft_c41_c5.contact_cfdata_id(+))
and (t.organization_id = '00Dq0000000Ai3k')
and (t.custom_entity_data_id != '000000000000000')
and (t.key_prefix = ?)
and (t_c41.organization_id = '00Dq0000000Ai3k')
and (t_c41.custom_entity_data_id != '000000000000000')
and (t_c41.key_prefix = ?)
and (t_c41_6.organization_id = '00Dq0000000Ai3k')
and (t_c41_6.deleted = '0')
and (t_c41_6.table_enum_or_id not in ('Idea', 'Question', 'Reply')
or t_c41_6.table_enum_or_id is null)
and (t_c41_c9.organization_id(+) = '00Dq0000000Ai3k')
and (t_c41_c9.key_prefix(+) = ?)
and (t_c41_c5.organization_id(+) = '00Dq0000000Ai3k')
and (cft_c41_c5.organization_id(+) = '00Dq0000000Ai3k'))
where (("IsDeleted_gen_1" = '0')
AND ("IsDeleted_gen_2" = '0')
AND (nvl("IsDeleted_gen_3",'0') <> '1')
AND (nvl("IsDeleted_gen_4",'0') <> '1')
AND ("Status__c" = ?)
AND ("Delivery_Mode__c" = ?)
AND (upper("t_c41_6_Name") = ?)
AND (nvl("t_c41_c5_Assigned__c",'0') <> '1')
AND ("t_c41_Unit_of_Interest__c" is not null and "t_c41_Unit_of_Interest__c" > '000000000000000')
AND (nvl("t_c41_c9_Assigned__c",'0') <> '1')
AND ("Call_Back_Time__c" <= to_date(?))
AND ("OwnerId" = ?))
order by "Call_Back_Time__c" asc nulls first)
where rownum <= ?
14:11:43.102 (102006532)|HEAP_ALLOCATE|[26]|Bytes:2473
14:11:43.102 (102053402)|METHOD_EXIT|[8]|01pq0000000Ad4T|WorkItemQueue.fetchWorkItem()
14:11:43.102 (102068549)|SYSTEM_MODE_EXIT|false
14:11:43.102 (102162687)|FATAL_ERROR|System.UnexpectedException: common.exception.SfdcSqlException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0


select /*Apex.Class.WorkItemQueue.fetchWorkItem: line 26*/ *
from (select "Id",
"Priority__c",
"Response__c"
from (select /*+ ordered index(t iecustom_entity_data_owner) use_nl(t_c41) use_nl(t_c41_6) use_nl(t_c41_c9) use_nl(t_c41_c5) use_nl(cft_c41_c5) index(cft_c41_c5 PKCONTACT_CFDATA) */
t.deleted "IsDeleted_gen_1",
t_c41.deleted "IsDeleted_gen_2",
t_c41_c5.deleted "IsDeleted_gen_3",
t_c41_c9.deleted "IsDeleted_gen_4",
t.val7 "Status__c",
t.val48 "Delivery_Mode__c",
t_c41_6.name "t_c41_6_Name",
cft_c41_c5.val59 "t_c41_c5_Assigned__c",
t_c41.val9 "t_c41_Unit_of_Interest__c",
t_c41_c9.val156 "t_c41_c9_Assigned__c",
TO_DATE(t.val58) "Call_Back_Time__c",
t.owner "OwnerId",
t.custom_entity_data_id "Id",
TO_NUMBER(t.val4) "Priority__c",
t.val41 "Response__c"
from core.custom_entity_data t,
core.custom_entity_data t_c41,
core.record_type t_c41_6,
core.custom_entity_data t_c41_c9,
sales.contact t_c41_c5,
sales.contact_cfdata cft_c41_c5
where (t.val41 = t_c41.custom_entity_data_id)
and (t_c41.record_type_id = t_c41_6.record_type_id)
and (t_c41.val9 = t_c41_c9.custom_entity_data_id(+))
and (t_c41.val5 = t_c41_c5.contact_id(+))
and (t_c41_c5.contact_id = cft_c41_c5.contact_cfdata_id(+))
and (t.organization_id = '00Dq0000000Ai3k')
and (t.custom_entity_data_id != '000000000000000')
and (t.key_prefix = ?)
and (t_c41.organization_id = '00Dq0000000Ai3k')
and (t_c41.custom_entity_data_id != '000000000000000')
and (t_c41.key_prefix = ?)
and (t_c41_6.organization_id = '00Dq0000000Ai3k')
and (t_c41_6.deleted = '0')
and (t_c41_6.table_enum_or_id not in ('Idea', 'Question', 'Reply')
or t_c41_6.table_enum_or_id is null)
and (t_c41_c9.organization_id(+) = '00Dq0000000Ai3k')
and (t_c41_c9.key_prefix(+) = ?)
and (t_c41_c5.organization_id(+) = '00Dq0000000Ai3k')
and (cft_c41_c5.organization_id(+) = '00Dq0000000Ai3k'))
where (("IsDeleted_gen_1" = '0')
AND ("IsDeleted_gen_2" = '0')
AND (nvl("IsDeleted_gen_3",'0') <> '1')
AND (nvl("IsDeleted_gen_4",'0') <> '1')
AND ("Status__c" = ?)
AND ("Delivery_Mode__c" = ?)
AND (upper("t_c41_6_Name") = ?)
AND (nvl("t_c41_c5_Assigned__c",'0') <> '1')
AND ("t_c41_Unit_of_Interest__c" is not null and "t_c41_Unit_of_Interest__c" > '000000000000000')
AND (nvl("t_c41_c9_Assigned__c",'0') <> '1')
AND ("Call_Back_Time__c" <= to_date(?))
AND ("OwnerId" = ?))
order by "Call_Back_Time__c" asc nulls first)
where rownum <= ?
 
Best Answer chosen by Ramu Veligeti 5
Gordon EngelGordon Engel
A ListView would execute a similar query to the one you tried, so if the ListView fails when you add 'Call back time', then it supports my theory that there's a bad row in the table.  Unfortunately your image upload didn't appear in the forum so I can't see for sure if that's what you are telling me.

I think there are 3 options:

1) Open a case with Salesforce and ask them to run a 'scrutiny' on the sandbox org to check for bad records
2) Try to find the bad records on your own and fix them
3) Refresh the sandbox and see if the problem goes away.

If it turns out that there's some other problem going on, like a Salesforce bug, then #1 will be the fastest path to resolve that, too.
 

All Answers

Gordon EngelGordon Engel
One possibility is that you somehow have bad row(s) in your Winter '16 sandbox copy.  You could try some queries with workbench to find the row(s).

I didn't see any known issues specific to this in Winter '16, but it is a fairly common error.
 
Ramu Veligeti 5Ramu Veligeti 5
Thanks for your reply Gordon. I don't think this is an issue with bad data as I tried creating a view on the same object adding 'Call back time' field as one of the columns and Salesforce is failing to give the view. [image: Inline images 1]
Gordon EngelGordon Engel
A ListView would execute a similar query to the one you tried, so if the ListView fails when you add 'Call back time', then it supports my theory that there's a bad row in the table.  Unfortunately your image upload didn't appear in the forum so I can't see for sure if that's what you are telling me.

I think there are 3 options:

1) Open a case with Salesforce and ask them to run a 'scrutiny' on the sandbox org to check for bad records
2) Try to find the bad records on your own and fix them
3) Refresh the sandbox and see if the problem goes away.

If it turns out that there's some other problem going on, like a Salesforce bug, then #1 will be the fastest path to resolve that, too.
 
This was selected as the best answer
Ramu Veligeti 5Ramu Veligeti 5
Thanks Gordon. You are right there is some bad data. I updated Call back time to NULL for all the records in the object and it worked like charm. Not sure how salesforce allowed to create bad date time. 
Ramu Veligeti 5Ramu Veligeti 5
Actually updating all the records didn't work initially. So, I updated them to some date time and then updated them to NULL and it worked.