oracle - Invalid number error - [Error Code: 1722, SQL State: 42000] ORA-01722: invalid number -

the 1st query below 2 queries giving me [error code: 1722, sql state: 42000] ora-01722: invalid number error. when limit no of records in 2nd query running fine. other limiting rows in 2nd query, both queries identical.

select b.first_name,        b.last_name,        b.device_derived,        b.ios_version_group,        b.add_date,        first_value (b.add_date)          on (partition b.first_name, b.last_name, b.ios_version_group)          first_date,        last_value (b.add_date)          on (partition b.first_name, b.last_name, b.ios_version_group)          last_date   (select a.first_name,                a.last_name,                a.os_version,                a.device_type,                a.device,                a.add_date,                a.device_derived,                case                  when (    (   upper (a.device_derived) = 'iphone'                             or upper (a.device_derived) = 'ipad')                        , to_number (substr (a.os_version, 1, 1)) > 4)                                     'ios ' || substr (a.os_version, 1, 1)                  else                    'others'                end                  ios_version_group           (select first_name,                        last_name,                        os_version,                        device_type,                        device,                        add_date,                        case                          when upper (device_type) = 'android'                                                     'android'                          when    upper (device_type) = 'bb'                               or upper (device_type) = 'blackberry'                                                     'blackberry'                          when     upper (device_type) = 'ios'                               , (   substr (upper (device), 1, 6) = 'iphone'                                    or substr (upper (device), 1, 4) = 'ipod')                                                     'iphone'                          when     upper (device_type) = 'ios'                               , (substr (upper (device), 1, 4) = 'ipad')                                                     'ipad'                        end                          device_derived                   vw_mobile_devices_all) a) b;    select b.first_name,        b.last_name,        b.device_derived,        b.ios_version_group,        b.add_date,        first_value (b.add_date)          on (partition b.first_name, b.last_name, b.ios_version_group)          first_date,        last_value (b.add_date)          on (partition b.first_name, b.last_name, b.ios_version_group)          last_date   (select a.first_name,                a.last_name,                a.os_version,                a.device_type,                a.device,                a.add_date,                a.device_derived,                case                  when (    (   upper (a.device_derived) = 'iphone'                             or upper (a.device_derived) = 'ipad')                        , to_number (substr (a.os_version, 1, 1)) > 4)                                     'ios ' || substr (a.os_version, 1, 1)                  else                    'others'                end                  ios_version_group           (select first_name,                        last_name,                        os_version,                        device_type,                        device,                        add_date,                        case                          when upper (device_type) = 'android'                                                     'android'                          when    upper (device_type) = 'bb'                               or upper (device_type) = 'blackberry'                                                     'blackberry'                          when     upper (device_type) = 'ios'                               , (   substr (upper (device), 1, 6) = 'iphone'                                    or substr (upper (device), 1, 4) = 'ipod')                                                     'iphone'                          when     upper (device_type) = 'ios'                               , (substr (upper (device), 1, 4) = 'ipad')                                                     'ipad'                        end                          device_derived                   vw_mobile_devices_all) a) b  rownum <= 100; 

can tell me why getting error. there efficient way write query?

you have to_number (substr (a.os_version, 1, 1) in queries, presumably you're hitting data doesn't have number @ start of os_version, when request more 100 rows.

you need check data.