Home » SQL & PL/SQL » SQL & PL/SQL » how to use floor(to_number(substr(x))) in where clause?
how to use floor(to_number(substr(x))) in where clause? [message #1005] Wed, 20 March 2002 09:42 Go to next message
George
Messages: 68
Registered: April 2001
Member
Hi,
I have a column doc_id which value like abcd.01.023. If I
select floor(to_number(substr(doc_id, instr(doc_id, '.', 1)+1, instr(doc_id, '.', 1)+1 )))
it works,
but get ORA-01722: invalid number if I use in where clause like:

Where floor(to_number(substr(doc_id, instr(doc_id, '.', 1)+1, instr(doc_id, '.', 1)+1 ))) =0

Could you tell me how to fix?

Thansk
Re: how to use floor(to_number(substr(x))) in where clause? [message #1015 is a reply to message #1005] Wed, 20 March 2002 23:50 Go to previous message
Manoj
Messages: 101
Registered: August 2000
Senior Member
hi George ,

it is work properly. use it
select floor(to_number(substr('abcd.01.023', instr('abcd.01.023', '.', 1)+1, instr('abcd.01.023', '.', 1)+1 ))) f1
from dual
Where floor(to_number(substr('abcd.01.023', instr('abcd.01.023', '.', 1)+1, instr('abcd.01.023', '.', 1)+1 ))) =1
Previous Topic: varchar=numeric in where clause
Next Topic: How to drop table in After Report Trigger in Reports?
Goto Forum:
  


Current Time: Fri Mar 29 03:21:32 CDT 2024