ORA-01722: invalid number

After a recent upgrade to 10g, a query that used to work is failing....

I'm attempting to run the following query:

SELECT DISTINCT id3, id1, id2
           FROM (
           
                  SELECT SUBSTR (stvrdef_code, 1, 1) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                  WHERE f_valid_number(SUBSTR (stvrdef_code, 1, 1)) = 'Y'
                 
                )
          WHERE id1 BETWEEN 8 AND 9
       ORDER BY id1, id2

Some sample data from the stvrdef table:

STVRDEF_CODE      STVRDEF_DESC

9FTL              Flooring-tile/linoleum
9FWD              Flooring-wood
9WBL              Windows-black
9WNO              Windows-no
9WYS              Windows-yes
AC              Air conditioning
ADA              ADA-Wheelchair Accessible
CA1              Chart-anatomical
CCM1              Computer Classroom-multi-units

Ok, obviously Oracle is choosing to look at the STVRDEF table prior to filtering out only cases where the first character of the CODE is numeric. But, I've tried rearraning this statement in many ways and am having no success.

Points to the first one with a working SQL statement that does what I need.

TIA!
LVL 11
L00MAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
SELECT DISTINCT id3, id1, id2
           FROM (
           
                  SELECT Cast(SUBSTR (stvrdef_code, 1, 1) as Number) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                  WHERE f_valid_number(SUBSTR (stvrdef_code, 1, 1)) = 'Y'
                 
                )
          WHERE id1 BETWEEN 8 AND 9 And
       ORDER BY id1, id2
0
L00MAuthor Commented:
Same error, and TOAD highlights 'stvrdef' in the sub query when it throws the error.
0
Jinesh KamdarCommented:
What is the code for the function f_valid_number ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mfsamuelCommented:
try this...
SELECT DISTINCT id3, id1, id2
           FROM (
           
                  SELECT SUBSTR (stvrdef_code, 1, 1) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                  WHERE length(translate(trim(SUBSTR(stvrdef_code, 1, 1)),' +-.0123456789',' '))=0
                  
                )
          WHERE id1 BETWEEN 8 AND 9
       ORDER BY id1, id2

Open in new window

0
L00MAuthor Commented:
That function is used all over the place with no problems.
Also, if I remove the Where clause, it works fine too.
CREATE OR REPLACE FUNCTION EKUSMGR.f_valid_number(data IN VARCHAR2 )
RETURN VARCHAR2
 
---------------------------------------------------
-- Purpose: Returns 'Y' if number, 'N' if not
--------------------------------------------------
 
IS
 
num NUMBER;
 
BEGIN
 
  num := TO_NUMBER(data);
  RETURN 'Y';
  
EXCEPTION
  WHEN OTHERS 
    THEN RETURN 'N';
  
END;
/

Open in new window

0
L00MAuthor Commented:
@mfsamuel:
Same error in the same spot...
0
mfsamuelCommented:
you may need to do a cast if the above doesn't work.
SELECT DISTINCT id3, id1, id2
           FROM (
           
                  SELECT SUBSTR (stvrdef_code, 1, 1) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                  WHERE length(translate(trim(SUBSTR(stvrdef_code, 1, 1)),' +-.0123456789',' '))=0
                  
                )
          WHERE CAST(id1 AS NUMBER(38)) BETWEEN 8 AND 9
       ORDER BY id1, id2

Open in new window

0
mfsamuelCommented:
or with your function...
SELECT DISTINCT id3, id1, id2
           FROM (
           
                  SELECT SUBSTR (stvrdef_code, 1, 1) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                 WHERE f_valid_number(SUBSTR (stvrdef_code, 1, 1)) = 'Y'
                  
                )
          WHERE CAST(id1 AS NUMBER(38)) BETWEEN 8 AND 9
       ORDER BY id1, id2

Open in new window

0
Jinesh KamdarCommented:
Well, without using ur function, there are many simpler ways to achieve this.
SELECT DISTINCT id3, id1, id2
           FROM ( SELECT SUBSTR (stvrdef_code, 1, 1) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                  WHERE SUBSTR(stvrdef_code, 1, 1) BETWEEN 0 AND 9)

Open in new window

0
L00MAuthor Commented:
@mfsamuel:
The first sample code you posted runs, but returns no records.
The second one fails at the same spot as before.

@jinesh kamdar:
Yours fails at the same spot as above, same error
0
Jinesh KamdarCommented:
My mistake. I forgot the quotes.
SELECT DISTINCT id3, id1, id2
           FROM ( SELECT SUBSTR (stvrdef_code, 1, 1) id1,
                         SUBSTR (stvrdef_code, 2, 1) id2,
                         SUBSTR (stvrdef_code, 1, 2) id3
                  FROM stvrdef
                  WHERE SUBSTR(stvrdef_code, 1, 1) BETWEEN '0' AND '9')
WHERE id1 BETWEEN 8 AND 9
ORDER BY id1, id2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
L00MAuthor Commented:
I was so close!
Thanks jinesh!
0
Jinesh KamdarCommented:
Glad to be of help :)
0
mfsamuelCommented:
if you run just the nested query what does it return?
0
mfsamuelCommented:
ignore.  forgot to refresh page.  

good solution jinesh
0
L00MAuthor Commented:
mfsamuel
01.31.2008 at 01:34PM EST, ID: 20789520
if you run just the nested query what does it return?

Just in case you are curious... it returned the expected records.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.