• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3357
  • Last Modified:

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!
0
L00M
Asked:
L00M
  • 6
  • 5
  • 4
  • +1
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now