Member Avatar for 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

  • 4 Contributors
  • 4 Replies
  • 130 Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by naziatarannum
Member Avatar for debasisdas

INSTR(rounded_cost, '.' -1)

what does the above part do ?

Member Avatar for janki066

what is the result of your substr you need

Member Avatar for 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

Member Avatar for naziatarannum

What values does this rounded_cost attribute consists of?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.