[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 22962
  • Last Modified:

Error -6502: ORA-06502: PL/SQL: numeric or value error:

very urgent, please help.
i am getting this error and ihave tried to rmove the spaces in the data and stuff. it din work. is there ary proceudre i can use to remove all spaces or junk values like # or % and stufff..

error:
Error -6502: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

one piece of code, where the error is coming, i guess:

IF TRUNC (
TO_NUMBER (
LTRIM (RTRIM (diag_rec.sec_diag6)))) = TRUNC (TO_NUMBER (v_icd_main))
THEN
v_icd := v_icd+ 1;
END IF;

datatypes associated with that:
v_icd_main VARCHAR2 (50)
v_icd VARCHAR2 (50)
v_icd250 VARCHAR2 (50) := 0;


it works for a small dataset with data. when i try a huge datasset somewhere its geting messed up. i am asuming its some space in some blank fields.
how do i remove spaces inside data columns.

0
ezee
Asked:
ezee
1 Solution
 
kretzschmarCommented:
well
just store it in variables before you compare

like
begin
  begin
    v_1 := TRUNC (TO_NUMBER (LTRIM (RTRIM (diag_rec.sec_diag6))));
    v_2 := TRUNC (TO_NUMBER (v_icd_main));
  exception
    when others then --or other handling
      v_continue := false;
  end;
  if v_continue then
    if v_1=v_2 then
      ...
    end if;
  else
    ---maybe other handling
  end if;
....

just from head as suggestion

meikl ;-)


0
 
mnicorasCommented:
Hi,

I suggest to use the function replace(diag_rec.sec_diag6,' ') instead of LTRIM(RTRIM(...

ok?

best regards,
Marius Nicoras
0
 
kretzschmarCommented:
maybe the translate function helps->just evaluating now
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
kretzschmarCommented:
well, translate could do the job like

select
translate('acbd+20.3#$%','1234567890.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzß!"§$%&/()=?+*#-_:;',
                         '1234567890.                                                                       ') from dual

results in 20.3 and many some blanks, which could be trimed away ltrim/rtrim as you already do it above

meikl ;-)
0
 
pennnnCommented:
kretzschmar,
If you use the following syntax in your translate statement, there won't be any spaces to be trimmed:
select
translate('acbd+20.3#$%','1234567890.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzß!"§$%&/()=?+*#-_:; ',
                        '1234567890.') from dual

If a space is the only possible "bad" character, then Marius' solution using replace is better, or at least more readable...
I think the real questions are:
- If the field is supposed to contain numbers why isn't its datatype set to NUMBER?
- If the field is supposed to contain numbers why does it contain other characters?
- If it does contain other characters, why the heck are you going to convert them to numbers? How are you going to decide what number is the following string: "9# 9"?! Is it 99? At least that's what you're going to get using kretzschmar's query.

If your fields are supposed to contain only numbers, then convert them to a NUMBER datatype or at least perform a one-time update (using translate or replace or whatever) to get rid of the "bad" characters!
Hope that helps!
0
 
kretzschmarCommented:
well, was adhoc, pennnn with 4n ;-)
0
 
kretzschmarCommented:
btw.
i prefer my first comment,
which just rejects unusual records,
they could be logged and an
user could then correct the entries

but it depends on the needs of the questioner

meikl ;-)
0
 
kelfinkCommented:
ezee,

Looks like you're handling ICD9 or ICD10 coding.

In case you're just gettign into that stuff, remember that there are V codes, and, I think some others, where the code starts with a letter.  Taking the letter off is not valid, I think.  Changes the meaning of the code.

It's been a few years since I worked with diagnosis codes.

I certainly never performed any math operations on them.  Maybe you're rounding up, to find the general code instead of a specific one?

If you want to perform those operations, I'd take the first letter off (if it's a letter) then put it back on later.
0
 
kretzschmarCommented:
hello?
0
 
ezeeAuthor Commented:
i didn't follow any of the above. But would like to split the points to kretzschmar and pennnn. I would like to thank kelfink for his advice, as it makes sense, for my work.
how do i split the points.
0
 
kretzschmarCommented:
ask a 0-pts question at community support
for splitting points
0
 
ezeeAuthor Commented:
i din get u. but i made a 0-point question but i guess i made a mistake by giving 250 points. it doent take 0 points for that. i really don get it. sorry. please explain.
0
 
kretzschmarCommented:
goto
http://www.experts-exchange.com/commspt/

ask there a question for splitting points like
(assign 0 points to this new q there)

please split points for my q
http://www.experts-exchange.com/oracle/Q_20321264.html
between kretzschmar and pennnn (with 4n ;-)

a moderator will then pick this question and assist you

delete your 0-point question here

hope i was clear enough

meikl ;-)
0
 
ezeeAuthor Commented:
kretzschmar: I have decided to award the full points to you, as I happened to use your idea in my recent data cleaning operation and it worked like charm.
Thanks a lot.
ks
0
 
kretzschmarCommented:
oops,
never guessed that
thanks :-)

glad to helped you,
good luck again

meikl ;-)

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now