ORA-01722 invalid number - help 2

[cmmrfrds]

I am trying to cast a field to numeric(38,0) to be able to join on. The field I am joining on is defined as
TX_ID NUMBER(38)

I tried using number(38) in the cast with the same error. Do I need to format the number somehow?

Select tdl.tx_id,
post_date,
invoice_number,
amount
From clarity.clarity_tdl_tran tdl
inner join
(
SELECT
ROWID,
INVOICE_ID,
INVOICE_NUM,
EOB_ICN,
TRANSACTION_LIST,
cast(nvl(TRANSACTION_LIST,0) as numeric(38,0)) as tx_id
FROM CLARITY.INV_CLM_LN_ADDL Tbl
) inv
On inv.tx_id = tdl.tx_id
where tdl.detail_type = 50
and tdl.post_date >= to_date('09/01/2005','MM/DD/YYYY')
and rownum < 20

 

[SantaMufasa]

Cmmrfrds,

Since Oracle does automatic "re-casting" from character expressions to numeric on an as-needed basis, what happens if you change the code,

"cast(nvl(TRANSACTION_LIST,0) as numeric(38,0)) as tx_id" to simply read,

"TRANSACTION_LIST tx_id"?

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cmmrfrds]

I just did that and it still comes up with the same error message. If I look at the data without the join the numbers all look good.

 

[LKBrwnDBA]

Besides, the correct syntax would be:

cast(nvl(TRANSACTION_LIST,0) as NUMBER(38,0)) as
tx_id




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Cmmrfrds,

Since my crystal ball is very cloudy today and I'm not the best guesser, could you please post a copy and paste of your SQL*Plus run of the code and its accompanying error message?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cmmrfrds]

I had it like this when I first got the error.

cast(nvl(TRANSACTION_LIST,0) as NUMBER(38,0)) as
tx_id

 

[Turkbear]

Hi,
Have you confirmed that ALL Transaction_List values are numeric literals?
The error you get indicates maybe not:

ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cmmrfrds]

How would I scan through the field looking for non-numeric characters? I am not sure how to catch something like this.
123A4546 in the field. Is there a isnumeric function in ORacle?

 

[cmmrfrds]

turkbear, you helped me stumble on the answer. I guess the name should have given me a clue.

SELECT
ROWID,
INVOICE_ID,
INVOICE_NUM,
EOB_ICN,
TRANSACTION_LIST
--cast(nvl(TRANSACTION_LIST,0) as number(38,0)) as tx_id
FROM CLARITY.INV_CLM_LN_ADDL Tbl
where transaction_list like '%,%'

I found some values like this.
TRANSACTION_LIST
1036476,1036475
1037340,1037338
1037340,1037338

Is there an easy way to just pick up the first number in this list.

 

[SantaMufasa]

Sure,

If the offenders all have commas (as their offending characters), then you could isolate the first value by using a decode function similar to this:

select decode(instr(transaction_list,',')
             ,0,transaction_list
             ,substr(transaction_list,1,instr(transaction_list,',')-1)
             ) Result
from dual;

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
Try,

SELECT 
   ROWID,
   INVOICE_ID,
   INVOICE_NUM, 
   EOB_ICN,
  [B][I] substr(TRANSACTION_LIST,1,Instr(TRANSACTION_LIST,',') -1)[/I][/B]
   --cast(nvl(TRANSACTION_LIST,0) as number(38,0)) as tx_id 
FROM CLARITY.INV_CLM_LN_ADDL Tbl
where transaction_list like '%,%'

Should only get the first part..If no non-numeric stuff shows up, use the substr function in your Cast statement.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cmmrfrds]

Thank you for the examples. I went through the data and discovered that 1% of the records have 2 numbers in the list and that about .005% have 3 numbers in the list. My next challenge is to isolate the 2nd and 3rd number that will be all.

What I plan on doing, which I can handle okay is to make a materialized view with each of these numbers being a separate row. I will have 3 queries unioned together, the 1st will create a row for the 1st number, the 2nd will create a row for the 2nd number, and the 3rd for the 3rd number. The challenge is to isolate the 2nd and 3rd number preferably in the Where clause to pull only the records I want for the 2nd and 3rd queries. Any help is appreciated on how to isolate the 2nd and 3rd number.

Thank you. This has been a great help.

 

[SantaMufasa]

Again, Sure...You can use my "UNSTRING" function:

Section 1 -- "UNSTRING" function definition:

create or replace function unstring
    (str_in varchar2
    ,delim varchar2
    ,which number)
    RETURN varchar2
is
begin
    if which = 1 then
        return substr(str_in,1,instr(str_in,delim)-1);
    else
        return substr(str_in
            ,instr(str_in,delim,1,which-1)+1
            ,(instr(str_in,delim,1,which)-instr(str_in,delim,1,which-1))-1);
    end if;
end;
/

Function created.

Section 2 -- Some sample data:

SQL> select TRANSACTION_LIST from INV_CLM_LN_ADDL;

TRANSACTION_LIST
----------------------------------------
1037338
1036476,1036475
1036476,1036475,1036474

Section 3 -- Sample invocations of "UNSTRING":

col a heading "Value 1" format a7
col b heading "Value 2" format a7
col c heading "Value 3" format a7
var comma char
exec :comma := ','
select unstring(TRANSACTION_LIST||:comma,:comma,1) a
      ,unstring(TRANSACTION_LIST||:comma,:comma,2) b
      ,unstring(TRANSACTION_LIST||:comma,:comma,3) c
from INV_CLM_LN_ADDL;

Notice in the above sample invocation, it doesn't matter whether you have 0,1,2,3, or any number of values in the subject expression that you are passing to "UNSTRING", but you must end the string with your delimiter (in this case a comma), which is why I concatenated a comma to TRANSACTION_LIST. You can supply any delimiter you want, but whatever delimiter you specify to "UNSTRING", that is the only delimiter that the function looks for.

The third argument is which delimited occurrence out of the source sting you want.

In review:
Argument-1: Subject string to search
Argument-2: Delimiter string that terminates each occurrence within the subject string
Argument-3: Which occurrence you want from within the subject string.

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Oops, I forgot the output listing of the sample (proof-of-concept) invocation:

SQL> select unstring(TRANSACTION_LIST||:comma,:comma,1) a
  2        ,unstring(TRANSACTION_LIST||:comma,:comma,2) b
  3        ,unstring(TRANSACTION_LIST||:comma,:comma,3) c
  4  from INV_CLM_LN_ADDL;

Value 1 Value 2 Value 3
------- ------- -------
1037338
1036476 1036475
1036476 1036475 1036474

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cmmrfrds]

Thank you very much, I should have enough to go on.