sql - Use subquery with multiple rows

Question

I have been trying to work out code in SQL to clean up a data sheet (more than 200 rows and 50 columns) to add trailing zeros before the decimal point values.

I tried to apply a to_char to convert string data into a 0 padded figure, for all values less than 1

select to_char((select "1980" from imf_population where "1980" <1), '0.999')
from imf_population 

However due to the subquery the to_char cannot perform a conversion on multiple rows returned from the 1980 column as there is more one record whose value is less than 1.

Any tips on how to get around this?

Answer 1

Note : Only Use Lowercase Letters, Numbers, and Underscores when naming columns. Use Simple, Descriptive Column Names

 CREATE TABLE imf_population (col varchar2(20) )

 INSERT INTO imf_population (col) VALUES ('0.5289')

select to_char(col,'0.999') from imf_population where col<1;

| TO_CHAR(COL,'0.999') |
| :------------------- |
|  0.529               |

db<>fiddle here