Hi Guys,

Today I came across one little bit funny piece of post at one of the thread in Oracle Forum.

Member No 1 asked following doubts:

Hi all,
I have the table

List
flag_circ char(1)
impo number(11,3)

and I have to update flag_circ, if the number of decimal of the field impo, are 0 or 1

For example
Impo value
123,444 –> no update
123,44 –> no update
123,4 –> yes update
123 –> yes update

I’m trying those functions like round, mod, trunc, but don’t work!

Some suggestions?

Member No 2 answers:

Use this
declare
cursor c_1 is select rowid row_id from table where (round(number_field,0) = number
or round (number_field,1)= number;
begin
for f_1 in c_1 loop
update table(repplcae by table name here) set flag = ‘Y’ where rowid = f_1.row_id;
end;
/
I think this will work

Member No 3 answers to Member No 2:

It may work but it is horrible.

What would motivate you to declare a cursor and do in a loop what can easily be done with a single update statement?

A desire to write the slowest least efficient code possible?

If you have written code like this at work please do yourself and your employer a favor and get rid of the cursor loop.

There is from 9i onward almost no excuse for ever writing a cursor loop.

Now lets think about it, its not really funny but do tell us what not to do!

Any way my answer was

Hi,

Try this

UPDATE list
SET flag_circ = somthing you want
WHERE INSTR(impo,’.',1,1) = 0
OR
LENGTH(SUBSTR(impo,INSTR(impo,’.',1,1)+1,2)) = 1

My tests:
SQL> WITH data_Set AS
2 (
3 SELECT 1 row_no, 1221.4554 text_col FROM dual
4 UNION ALL
5 SELECT 2 row_no, 1221.4554 text_col FROM dual
6 UNION ALL
7 SELECT 3 row_no, 1234 text_col FROM dual
8 UNION ALL
9 SELECT 4 row_no, 2343.5 text_col FROM dual
10 )
11 SELECT row_no FROM data_set
12 WHERE INSTR(text_col,’.',1,1) = 0
13 OR
14 LENGTH(SUBSTR(text_col,INSTR(text_col,’.',1,1)+1,2)) = 1
15 /

ROW_NO
———-
3
4

Regards,

Raj