PL/SQL to copy a number row to a varchar2 row w/ some formatting?
I have a table with the below fields. The VERSION_TEXT field is a new field I added. I had numbers such a 1.0 that would get turned into 1. So I added an extra field which will actually output the numbers. The new code supports both fields, but the existing data does not, so I need a PL/SQL script to populate the empty VERSION_TEXT row the same night the new code goes live.
ID (Number) which is the Primary Key
VERSION (Number) - Existing and has values
VERSION_TEXT (VarChar2) - New field w/ no values currently
Logic is as follows:
For each row in the table{
If the version value is a whole number{
update the empty version_text field to be the whole number followed by .0 (so 1 in version would be 1.0
in version_text)
}else{
Just copy the value from the version field to the version_text field ( 0.01 would get copied from version to
version_text as 0.01).
}
}
I figured I could just develope a java, perl, vb script. But if PL/SQL could handle this I would rather go that way and run the script directly against the DB. Not sure if this is something someone could write out in 2 minutes off the top of there mind? Provide me direction (don't have much PL/SQL experience)? Or if I'm better just doing it in another language? I figure the decimal part would be what might not be able to be done w/ PL/SQL, but I would think the rest wouldn't be too difficult? Thanks!
ID (Number) which is the Primary Key
VERSION (Number) - Existing and has values
VERSION_TEXT (VarChar2) - New field w/ no values currently
Logic is as follows:
For each row in the table{
If the version value is a whole number{
update the empty version_text field to be the whole number followed by .0 (so 1 in version would be 1.0
in version_text)
}else{
Just copy the value from the version field to the version_text field ( 0.01 would get copied from version to
version_text as 0.01).
}
}
I figured I could just develope a java, perl, vb script. But if PL/SQL could handle this I would rather go that way and run the script directly against the DB. Not sure if this is something someone could write out in 2 minutes off the top of there mind? Provide me direction (don't have much PL/SQL experience)? Or if I'm better just doing it in another language? I figure the decimal part would be what might not be able to be done w/ PL/SQL, but I would think the rest wouldn't be too difficult? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
awking00
case when mod(versionValue,1) = 0 then to_char(versionValue)||'.0
if you need more decimal places supported just add more 9's, but keep the first 0
to_char(version,'fm9999999
to_char(version,'fm9999999
also, rather than creating a new column that must be maintained, you might want to consider using a view that displays the string version as when needed
create or replace view my_view as
select id, version, to_char(version,'fm9999999
from your_table
create or replace view my_view as
select id, version, to_char(version,'fm9999999
from your_table