Salesforce Developer Community

Ramu Veligeti 5
NEWBIE
10 Points
Member since 2015

Chatter

Feed
0

Best Answers
0

Likes Received
0

Likes Given
1

Questions
3

Replies

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 <= ?

Ramu Veligeti 5
September 28, 2015
Like
0

SOLVED

5 replies

SOQL query with datetime failing in Winter 16

Ramu Veligeti 5
September 28, 2015
Like
0