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.
Comments
Post a Comment