Thanks for the question, Dmitry.
Asked: April 02, 2003 - 10:10 am UTC
Last updated: April 02, 2003 - 11:13 am UTC
Version: 9.2.0
Viewed 1000+ times
You Asked
Hi Tom!
Two quesitons:
1) I have FLOAT field, usually we format it in application and now i want to format it in query, format would looks like 8.0 -> 8, 8.1->8.1, 0.11 -> 0.11 and so on... is it possible using TO_CHAR?
2) I have table with two number fields f1 and f2 and want to implement such thing like unique constraint of 2 fields, but if f1 = 0 then several records possible. How can i implement this by your opinion?
and Tom said...
1) no, you would have to use some other string functions to clean out the characters you don't want. eg:
ops$tkyte@ORA920>
ops$tkyte@ORA920> select rtrim(rtrim(to_char(x,'99999999990.99'),'0'),'.') from t;
RTRIM(RTRIM(TO_
---------------
8
8.1
0.11
2) Using the fact that there are function based indexes and that entirely NULL entries are not made in b*tree indexes, we can:
ops$tkyte@ORA920> create table t ( f1 int, f2 int );
Table created.
ops$tkyte@ORA920> create unique index t_idx on
t( decode(f1,0,to_number(null),f1),
2 decode(f1,0,to_number(null),f2) );
Index created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 0, 1 );
1 row created.
ops$tkyte@ORA920> insert into t values ( 0, 1 );
1 row created.
ops$tkyte@ORA920> insert into t values ( 1, 1 );
1 row created.
ops$tkyte@ORA920> insert into t values ( 1, 1 );
insert into t values ( 1, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment