OraFAQ Forum: SQL & PL/SQL » TO_NUMBER in view - getting ORA-01722

Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER in view - getting ORA-01722 - invalid number

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TO_NUMBER in view - getting ORA-01722 - invalid number [message #174225]

Fri, 26 May 2006 10:59

butlerp@ramaz.org
Messages: 5
Registered: May 2006

Junior Member

I've created a view joining 2 files: a transaction file and its batch descriptor, which contains the words "Fiscal 2006 ..." or "Fiscal 2007 ..." where the ... represents a date.

Create View as File_Batch
(Field1,Fiscal_Yr_End)
AS
SELECT '1' as source,File1.Field1,
to_number(substr(BATCH."DESCRIPTION",8,4),'9999') as Fiscal_Yr_end from
File1,Batch
where File1.BatchID=Batch.BatchID
union
SELECT '2',File2.Field1,
File2.Fiscal_Yr_End from
File2

When I look at the file structure of view, the type of Fiscal_Yr_End is Number, length 22. I've tried the function To_number both with and without the '9999'.

If I run a query
Select Fiscal_Yr_End from Trans_Batch where Source='2' and Fiscal_Yr_End>2006
appropriate records are returned

If I run a query
Select Fiscal_Yr_End+100 from Trans_Batch where Source='1'
I get appropriate numbers.

However, when I try
Select Fiscal_Yr_End from Trans_Batch where Source='1' and Fiscal_Yr_End>2006

I get ORA-01722 - invalid number. So, it appears to me that it's something in the TO_NUMBER function.

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174234 is a reply to message #174225]

Fri, 26 May 2006 11:59

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Your problem is probably with your substr. If you run this:

SELECT substr(BATCH."DESCRIPTION", 8, 4)
FROM file1
    ,batch
WHERE file1.batchid = batch.batchid
AND source = '1'
ORDER BY substr(BATCH."DESCRIPTION", 8, 4)

I bet there will be a couple of records in there that are not numbers.

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174236 is a reply to message #174234]

Fri, 26 May 2006 12:43

butlerp@ramaz.org
Messages: 5
Registered: May 2006

Junior Member

Thanks for getting back to me.

No, there are no records in there that are not numbers. I'm able to do the following:

Select Fiscal_Yr_End+100 from Trans_Batch where Source='1'

I've seen posts re TO_NUMBER, which suggest the following:
using DECODE, TRANSLATE and NULL.

So I figured, instead of using TO_NUMBER by itself, I'd use what was suggested. The view was created, but when I query the view, I get Error: ORA-01427: single-row subquery returns more than one row. So there must be something wrong with the part that starts: SELECT DECODE...

create view XYZ
(TransBatch,Description,BatchID,Fiscal_Yr_End)
as
SELECT
POST_ARCASH.BATCH_ID,BATCH_ARCASH."DESCRIPTION",
BATCH_ARCASH."BATCH_ID",
( SELECT DECODE(
(REPLACE(
TRANSLATE(
TRIM(
to_number(substr(BATCH_ARCASH."DESCRIPTION",8,4))
),
'0123456789','00000000000')
,'0',NULL)),
NULL,to_number(trim(to_number(substr(BATCH_ARCASH."DESCRIPTION",8,4)))) ) FYE
FROM BATCH_ARCASH
)
FROM POST_ARCASH,BATCH_ARCASH
where
BATCH_ARCASH."BATCH_ID"=POST_ARCASH."BATCH_ID"

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174244 is a reply to message #174236]

Fri, 26 May 2006 13:41

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Whether or not you can run

Select Fiscal_Yr_End+100 from Trans_Batch where Source='1'

is irrelevant if you are using something like Toad or SQL Navigator. See the attachment. Toad and SQL Navigator only fetch the first 100 or so rows. It may be that the first 100 rows that match Source = '1' are just fine. When you narrow the search to Source='1' and Fiscal_Yr_End>2006, one of the first 100 records that match those criteria may not be a number. You could also try

Source='1' and Fiscal_Yr_End>'2006'

and see if you still get the error. Note the quotes around 2006. It will not give you the results you actually want, but it is a test for the substr not returning a number.

Attachment: This works fine.doc
(Size: 95.50KB, Downloaded 1056 times)

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174247 is a reply to message #174244]

Fri, 26 May 2006 14:04

butlerp@ramaz.org
Messages: 5
Registered: May 2006

Junior Member

I'm using a Microsoft ODBC driver for Oracle.

I really think it has something to do with the records that are coming from File1, where I use the function TO_NUMBER.
Create View as File_Batch
(Field1,Fiscal_Yr_End)
AS
SELECT '1' as source,File1.Field1,
to_number(substr(BATCH."DESCRIPTION",8,4),'9999') as Fiscal_Yr_end from
File1,Batch
where File1.BatchID=Batch.BatchID
union
SELECT '2',File2.Field1,
File2.Fiscal_Yr_End from
File2

The records that are coming from File2 are working fine.

Should I be doing something to the TO_NUMBER function? I know that every single record has 4 digits from positions 8 through 11.

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174254 is a reply to message #174247]

Fri, 26 May 2006 15:40

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

OK, I'll try this one more time. How do you "know" that every single record has 4 digits from positions 8 through 11? Did you actually look at every single record? Did you run a query to verify that? If so, what was the query? Actually, I don't need to know that. Here's how to find your problem. You can take my advice or continue to believe whatever it is you believe now. It doesn't matter to me.

SELECT sum(substr(BATCH."DESCRIPTION",8,4))
FROM   file1;

SELECT sum(File2.Fiscal_Yr_End)
FROM   file2;

One of them will fail. That is your problem table. If it is file1 then run this

SELECT substr(BATCH."DESCRIPTION",8,4), file1.*
FROM   file1
WHERE  translate(substr(BATCH."DESCRIPTION",8,4), '0123456789.', ' ') != ' ';

That, I'm guessing will show you the bad row(s). Or you could continue to guess. And if you reply, at least humor me and say you at least tried what I said and it didn't work. It doesn't help either of us if you simply keep repeating what you think it is.

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174256 is a reply to message #174247]

Fri, 26 May 2006 16:02

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

I agree with everything Scott said.

That said, does this work? (Warning: untested code.)

CREATE OR REPLACE VIEW file_batch (
    source
,   field1
,   fiscal_yr_end
)
AS
SELECT '1' source
,      f1.field1
,      TO_NUMBER(DECODE(REPLACE(TRANSLATE(SUBSTR(b.description,8,4)
                                          ,      '0123456789'
                                          ,      '9999999999')
                        ,       '9')
                 ,      NULL, SUBSTR(b.description,8,4))
       ,         '9999') fiscal_yr_end
FROM   file1        f1
,      batch        b
WHERE  f1.batchid = b.batchid
UNION ALL
SELECT '2'  source
,      f2.field1
,      f2.fiscal_yr_end
FROM   file2        f2
/

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174331 is a reply to message #174254]

Sat, 27 May 2006 22:58

butlerp@ramaz.org
Messages: 5
Registered: May 2006

Junior Member

Scott, your first suggestion gave me a valid sum (the sum of the 173 records (140*2006)+(33*2007). The 2nd suggestion returned the 173 records that I've been dealing with - I didn't understand from your email whether it was supposed to return just the bad rows.

Art, I was able to create your query, and then I was able to query FISCAL_YR_END>2006.

So using the DECODE(TRANSLATE... function works. I will use the Oracle manual to parse out what the statement means.

Thank you both for your help.

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174368 is a reply to message #174331]

Sun, 28 May 2006 13:52

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Really? They both worked? Hmmm. That should not have happened. When you ran Art's query, were any of the fiscal_yr_end values that were null? If I'm reading it right, it should return a null value for any value of the substr that had a non-numeric character in it, thus not causing the error you were receiving. Something still seems odd to me about what you say you are getting. You should not need Art's transformation for you query not to fail if all of your substr outputs are actually numbers. But if you and the folks you work for a satisfied with the solution, then I guess you should go with it.

Report message to a moderator

Re: TO_NUMBER in view - getting ORA-01722 - invalid number [message #174578 is a reply to message #174368]

Mon, 29 May 2006 19:50

butlerp@ramaz.org
Messages: 5
Registered: May 2006

Junior Member

That's what was so frustrating. I was never getting any records with invalid characters. I was always able to do "fiscal_yr_end *100" and get a valid number. Yet, I couldn't use it to compare.

As long as it works...

Thanks again for your help.

Report message to a moderator

Previous Topic:	oracle services
Next Topic:	Sunday Job

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 31 18:42:53 CDT 2025