weird query behavior oracle sql - convert text to number

oracle query This works

select *
from (
select to_number(substr(app_cluster,6,2), '99') as  b
from xtern_app_info
WHERE app_cluster IS NOT NULL
AND APP_CLUSTER <> 'CLUSTER'
);

enter image description here

but when adding 'where b > 2' makes an error, why?

select *
from (
select to_number(substr(app_cluster,6,2), '99') as  b
from xtern_app_info
WHERE app_cluster IS NOT NULL
AND APP_CLUSTER <> 'CLUSTER'
) where b > 2;

ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-01722: invalid number 29913. 00000 - "error in executing %s callout" *Cause: The execution of the specified callout caused an error. *Action: Examine the error messages take appropriate action.

1 answer

  • answered 2018-01-11 20:54 Ben

    This is known as a pushed predicate. Your predicate where b > 2 is being pushed into your sub-query as an optimisation, meaning that Oracle is executing:

    select to_number(substr(app_cluster,6,2), '99') as  b
      from xtern_app_info
     WHERE app_cluster IS NOT NULL
      AND APP_CLUSTER <> 'CLUSTER'
      AND to_number(substr(app_cluster,6,2), '99') > 2
    

    However, as you have some values 'CLUSTER' (or potentially some other unknown strings) in the APP_CLUSTER colum Oracle's checking whether to_number('ER') > 2, causing the error.

    Adding a hint to avoid predicate pushing should avoid this:

    select  *
      from ( select /*+ no_push_pred */ to_number(substr(app_cluster,6,2), '99') as  b
               from xtern_app_info
              WHERE app_cluster IS NOT NULL
                AND APP_CLUSTER <> 'CLUSTER'
                    ) 
     where b > 2;