ERROR at line 1: ORA-01722: invalid number

[Roybut]

Hi,

I have this view:

DROP VIEW digits;

CREATE VIEW digits
AS
(
    SELECT 
        TO_NUMBER(SUBSTR(rounded_cost, 1, INSTR(rounded_cost, '.' -1))) left_digits,
        TO_NUMBER(SUBSTR(rounded_cost, INSTR(rounded_cost, '.' + 1, LENGTH(rounded_cost)))) right_digits
    FROM roundcost
);

It allows me to create it but the values stored are apparently not valid numbers. When I try select * from digits I get
"ERROR at line 1: ORA-01722: invalid number ".

The roundcost table has numbers like 6.71, 3.54, etc.

Any ideas?

Thanks,

Roy

[debasisdas]

INSTR(rounded_cost, '.' -1)

what does the above part do ?

[janki066]

what is the result of your substr you need

[janki066]

SELECT
TO_NUMBER(SUBSTR(6.45, 1, INSTR(6.45, '.', 1))) left_digits,
TO_NUMBER(SUBSTR(6.45, INSTR(6.45, '.', - 1))) right_digits from dual

instead of 6.45 user your roundcost.

result will be 6 for left digit
and .45 for right digit

[naziatarannum]

What values does this rounded_cost attribute consists of?