oracle SQL return invalid data and way to find which data — oracle-mosc

PL/SQL (MOSC)

MOSC Banner

oracle SQL return invalid data and way to find which data

edited Apr 10, 2020 5:03PM in PL/SQL (MOSC) 5 commentsAnswered ✓

Hi  I have issue with SQL and when convert data to numeric  return error.  Then how to identify or skip invalid data to run the sql

SELECT *

FROM (

    SELECT to_number(trim(ariline_no)) , to_number(COUNT(CREW_no)) NONO

    FROM MAIN

    GROUP BY to_number(trim(ariline_no))

    ) C

error says

ORA-01722: invalid number

01722. 00000 -  "invalid number"

*Cause:    The specified number was invalid.

*Action:   Specify a valid number.

+++++++++++++++++++++

If I  alter this way  its works .

SELECT *

FROM (

    SELECT ariline_no, count(CREW_no) NONO

    FROM MAIN

    GROUP BY ariline_no

    ) C

++++++++++++++++++++++

but I need to find which data is cause of this issue

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center