Update number column value with 000 in oracle

Question

I am trying to update a column value. The column datatype is Number. As per the requirement, for the right records this column will be updated with 000. I have included this in the Else part of the condition but when the table is getting updated it's taking only 0 not 000. Please suggest. How can I make it 000?

MERGE INTO mem_src_extn t USING 
( 
SELECT mse.rowid row_id,
       CASE WHEN mse.type_value IS NULL OR mse."TYPE" IS NULL OR mse.VALUE_1 IS NULL or mse.VALUE_2 IS NULL THEN 100
            WHEN ( SELECT count(*) FROM cmc_mem_src cms WHERE cms.tn_id = mse.type_value ) = 0 THEN 222
            WHEN count(mse.value_1) over ( partition by type_value ) > 1 THEN 333
       ELSE 000 int_value_1 <-- here 
FROM   mem_src_extn mse
) u
ON ( t.rowid = u.row_id )
WHEN MATCHED THEN UPDATE SET t.int_value_1 = u.int_value_1

Column data type is number?. If yes Then you can not store it in that format. While retrieving data from that column for zero you replace it with 000. — Tharunkumar Reddy, Nov 01 '16 at 11:06
There is no number 000. That is a string, but as numbers, 0, 000, and 0.000 are all the same number: ZERO. — , Nov 01 '16 at 11:06
Zero as a number is stored as `0` internally. If you want to see `000` you will have to handle this in your presentation layer. As an alternative, you _could_ make the column text, but this isn't nice. — Tim Biegeleisen, Nov 01 '16 at 11:07
As an aside, you are missing the mandatory keyword END at the end of the CASE statement. So the code you posted does NOT insert ANYTHING into any table; care to explain? — , Nov 01 '16 at 11:27

Michael Piankov · Answer 1 · 2016-11-01T11:31:57.607

0

If your column int_value_1 is varchar2 use quote

MERGE INTO mem_src_extn t USING 
( 
SELECT mse.rowid row_id,
       CASE WHEN mse.type_value IS NULL OR mse."TYPE" IS NULL OR mse.VALUE_1 IS NULL or mse.VALUE_2 IS NULL THEN '100'
            WHEN ( SELECT count(*) FROM cmc_mem_src cms WHERE cms.tn_id = mse.type_value ) = 0 THEN '222'
            WHEN count(mse.value_1) over ( partition by type_value ) > 1 THEN 
'333'
       ELSE '000' int_value_1 
       END
FROM   mem_src_extn mse
) u
ON ( t.rowid = u.row_id )
WHEN MATCHED THEN UPDATE SET t.int_value_1 = u.int_value_1

but if you have a number as you say and want just to see 000 insead of 0 You may use to_char with format string

select to_char(int_value_1,'099') from mem_src_extn;

edited Nov 01 '16 at 11:31

answered Nov 01 '16 at 11:15

Michael Piankov

1,989
1
8
19

Did you see the part where the OP said the column type is NUMBER? And where is the required word END at the end of the CASE statement? – Nov 01 '16 at 11:25
Thank you @mathguy – Michael Piankov Nov 01 '16 at 11:32
What you added at the bottom is OK, but not related to the question, which was very clearly about what is stored in the database, not what is shown in a SELECT query. – Nov 01 '16 at 11:36

Update number column value with 000 in oracle

1 Answers1