You need to sign in to do that
Don't have an account?
LWI
Oracle error on soql query
Getting the following error:
Line: 64, Column: 1
System.UnexpectedException: common.exception.SfdcSqlException: ORA-01424: missing or illegal character following the escape character select /*Apex.Class.SetAccountRepRegion.execute: line 44*/ * from (select "Id", "Name", "State__c", "Country__c", "ZIP_prefix__c", "Type__c", "Rep_Region__c" from (select /*+ no_expand */ t.deleted "IsDeleted_gen_1", t.val4 "Country__c", t.val5 "Type__c", t.val6 "State__c", t.val3 "ZIP_prefix__c", t.custom_entity_data_id "Id", t.name "Name", t.val7 "Rep_Region__c" from core.custom_entity_data t where (t.organization_id = '[orgid]') and (t.custom_entity_data_id != '000000000000000') and (t.key_prefix = ?)) where (("IsDeleted_gen_1" = '0') AND ("Country__c" is null or upper("Country__c") is not null and upper("Country__c") in (select /*+ hash_sj swap_join_inputs(inlist) */ column_value from table(cast(? AS STRING_ARRAY)) inlist where column_value is not null)) AND (("Type__c" = ?) OR (("State__c" is null or upper("State__c") is not null and upper("State__c") in (select /*+ hash_sj swap_join_inputs(inlist) */ column_value from table(cast(? AS STRING_ARRAY)) inlist where column_value is not null)) AND ("Type__c" = ?)) OR (("ZIP_prefix__c" is not null and exists (select /*+ no_unnest */ * from table(cast(? AS STRING_ARRAY)) tc where upper("ZIP_prefix__c") like tc.column_value ESCAPE '\')) AND ("Type__c" = ?))))) where rownum <= ?
when running the soql query:
SELECT id, name, state__c, country__c, zip_prefix__c, type__c, rep_region__c
FROM sales_region_state__c
WHERE (State__c IN :states AND Country__c IN :countries AND Type__c='State') OR
(Zip_prefix__c LIKE :zmatchexprs AND Country__c IN :countries AND Type__c='Zip prefix') OR
(Country__c IN :countries AND Type__c='Country')];
Line: 64, Column: 1
System.UnexpectedException: common.exception.SfdcSqlException: ORA-01424: missing or illegal character following the escape character select /*Apex.Class.SetAccountRepRegion.execute: line 44*/ * from (select "Id", "Name", "State__c", "Country__c", "ZIP_prefix__c", "Type__c", "Rep_Region__c" from (select /*+ no_expand */ t.deleted "IsDeleted_gen_1", t.val4 "Country__c", t.val5 "Type__c", t.val6 "State__c", t.val3 "ZIP_prefix__c", t.custom_entity_data_id "Id", t.name "Name", t.val7 "Rep_Region__c" from core.custom_entity_data t where (t.organization_id = '[orgid]') and (t.custom_entity_data_id != '000000000000000') and (t.key_prefix = ?)) where (("IsDeleted_gen_1" = '0') AND ("Country__c" is null or upper("Country__c") is not null and upper("Country__c") in (select /*+ hash_sj swap_join_inputs(inlist) */ column_value from table(cast(? AS STRING_ARRAY)) inlist where column_value is not null)) AND (("Type__c" = ?) OR (("State__c" is null or upper("State__c") is not null and upper("State__c") in (select /*+ hash_sj swap_join_inputs(inlist) */ column_value from table(cast(? AS STRING_ARRAY)) inlist where column_value is not null)) AND ("Type__c" = ?)) OR (("ZIP_prefix__c" is not null and exists (select /*+ no_unnest */ * from table(cast(? AS STRING_ARRAY)) tc where upper("ZIP_prefix__c") like tc.column_value ESCAPE '\')) AND ("Type__c" = ?))))) where rownum <= ?
when running the soql query:
SELECT id, name, state__c, country__c, zip_prefix__c, type__c, rep_region__c
FROM sales_region_state__c
WHERE (State__c IN :states AND Country__c IN :countries AND Type__c='State') OR
(Zip_prefix__c LIKE :zmatchexprs AND Country__c IN :countries AND Type__c='Zip prefix') OR
(Country__c IN :countries AND Type__c='Country')];
Looke how the value of zmatchexprs is made.