Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

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 we 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

Comments

great thanks!

DiZ, April 02, 2003 - 10:48 am UTC

you save a lot of my time! especially with second question

DiZ, April 02, 2003 - 11:13 am UTC

I use rtrim(to_char(x,'fm9999999990.99'),'.') instead, couse
it doesn't left unnecessary spaces before value