| TO_CHAR and TO_NUMBER [message #6988] |
Wed, 14 May 2003 14:59  |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
I have a data item that is VARCHAR2 datatype, but the value that we store in it is the number of years for an action. If someone completes an action during the year, I need to increase the number by 1. I'm REALLY new to SQL, but I'm thinking that I should probably use TO_NUMBER to add 1 to the item, then change it all back to a character to update the VARCHAR2 item in the table. Here's what I'm doing and the error that I'm getting. Can you help? Thanks!
SET year_code = TO_CHAR(TO_NUMBER(year_code) + 1)
*
ERROR at line 2:
ORA-01722: invalid number
|
|
|
|
| Re: TO_CHAR and TO_NUMBER [message #6989 is a reply to message #6988] |
Wed, 14 May 2003 15:10  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Oracle will implicitly handle the conversions for you, so you could just:
update ...
set year_code = year_code + 1
where ...
I would guess that your error is caused by the fact that year_code, in some row somewhere, does not actually contain a number.
Your best option would really be to alter the datatype for that column to reflect the type of data that it stores. Storing numbers in string columns just leaves yourself wide open for invalid data - which is what is probably causing your error.
|
|
|
|
| Re: TO_CHAR and TO_NUMBER [message #6992 is a reply to message #6989] |
Wed, 14 May 2003 15:57 |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
Hi Todd,
Thanks for your reply. Actually, I tried that also, but got the same error. If I put it in a SELECT statement (Select years_code + 1), it accepts it as a number. But, it doesn't like it in an UPDATE statement. I am learning that UPDATE is the hardest command to get a hold of!
However, something else you said is nagging at my wee little brain...WHEN does oracle process the WHERE part of the statement? As you said, if Oracle found a years_code that was not really a number, but that non-number code was NOT to be included in the update because it was excluded by the WHERE statement, wouldn't Oracle process the WHERE statement and choose the appropriate rows BEFORE checking to see that one of the years_code was not really a number? I hope you can understand what I'm asking, but it only makes sense to me that the WHERE would be processed before the years_codes were all checked. BTW - I checked all of the years_codes for those that should be updated, and they all are numbers (this time, anyway). Your suggestion, none the less, is a very good one. I'll remember that!
Any other suggestions?
|
|
|
|
| Re: TO_CHAR and TO_NUMBER [message #6995 is a reply to message #6992] |
Wed, 14 May 2003 16:34 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Even if you let Oracle implicity handle the conversions, you'll still have the error if there is non-compliant data out there. Yes, Oracle will only update the rows identified by the WHERE clause, and there is no difference between a SELECT and an UPDATE. If there is "bad" data, both would raise the 'invalid number' error.
Can you identify the actual row that is causing the error? I still feel that there is some rogue data hiding out there...
|
|
|
|
| Re: TO_CHAR and TO_NUMBER [message #6996 is a reply to message #6992] |
Wed, 14 May 2003 16:53 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Susan,
Do you get any rows back from this SQL?SQL> SELECT year_code
2 FROM <i>your_table_here</i>
3 WHERE REPLACE(TRANSLATE(year_code
4 , '0123456789'
5 , '9999999999')
6 , '9'
7 , NULL) IS NOT NULL
8 /
YEAR_CODE
----------
17x9
SQL> |
|
|
|
| Re: TO_CHAR and TO_NUMBER [message #7034 is a reply to message #6995] |
Fri, 16 May 2003 12:21 |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
|
Thanks for all the help. You certainly put me on the right track. I was kind of mislead by the 'invalid number' error. My 'numbers' were really ok, but when Oracle added 1 to '00', the result was 1, not '01', and the 'invalid number' was really an integrity issue with the validation for the code which should have been 2 digits. So, LPAD took care of that and it now works.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
