sakthikumar
asked on
I Want 0 to be displayed in front of digits in number column
I have a number column in a table, It has more than 20 million records in it.
But now the users want some new records in this format(0123, 01455, ..).
I cannot change the datatype to varchar2, as the table is not empty.
Adding a new column , updating the new column with old, dropping the old and renaming the new is not possible as in many places inside the DB, there are inserts without column list.
Also adding a new column, updating the new from old, nullify the old and changing the datatype is not possible, because so many million records need to be updated 3 times. DBA is not accepting this solution.
Is there any other way?
But now the users want some new records in this format(0123, 01455, ..).
I cannot change the datatype to varchar2, as the table is not empty.
Adding a new column , updating the new column with old, dropping the old and renaming the new is not possible as in many places inside the DB, there are inserts without column list.
Also adding a new column, updating the new from old, nullify the old and changing the datatype is not possible, because so many million records need to be updated 3 times. DBA is not accepting this solution.
Is there any other way?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
johnsone
Alternately, you could create a view on the table that has the value formatted the way the user wants. No updating, or creating of columns needed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The gpal is to display one or more leading zeros? Then it's perhaps best addressed by setting the display mask:
COLUMN someval FORMAT 099.999
SELECT someval FROM sometab;
COLUMN someval FORMAT 099.999
SELECT someval FROM sometab;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I cannot change the datatype to varchar2, as the table is not empty. ---> Not just right, it would impact all the other code which refer to this column as a number but you are trying to change it to varchar2 now. So the best option is probably to go for a view and show the data in the format which is requried for your users instead of touching the table/columns.
Infact these type of things should be considered/done in the data design state and it would not be a direct / easy solution if that needs to happen after the current system has gone live is beign used already.
Infact these type of things should be considered/done in the data design state and it would not be a direct / easy solution if that needs to happen after the current system has gone live is beign used already.
So far this discussion has been to accommodate the new user request, and our comments presume there's a technical solution. Perhaps it's time to include why the users want this changed, what are the costs and benefits to the change, and to confirm senior management have signed off on this. I'm simply curious, as I have never seen this approach in my home country.
me too - had never come up this type of thing in the real sense. I had seen situations where there is a compelling need for this type of change and in those situations it would be dealt as a project/impact study etc.... because we are trying do deal with this after going far beyond and hence should be done carefully. The last thing anyone wants is an outage in your application because of some of your queries breaking or not working as desired/not producing results as desired.
ASKER
Already we have analyzed all the impacts, and the impacts are huge.
So, trying other solutions.
How I can use a view here, I am already having a table_name by one name,
how I can create an another object by the same name.
Please explain me clearly regarding the usage of view here.
So, trying other solutions.
How I can use a view here, I am already having a table_name by one name,
how I can create an another object by the same name.
Please explain me clearly regarding the usage of view here.
This would be for a new requirement correct? So, a new name wouldn't be out of the question, right?
If you need to be able to access both the table and the view with the same name, that isn't going to happen. If just the view is sufficient, then rename the table and create the view with the same name as the table. Now, you are going to have to thoroughly test insert/update/deletes to make sure they are going to work as you expect, as doing those against views isn't always what you hope it will do. You may need to create instead of triggers on the views.
If you need to be able to access both the table and the view with the same name, that isn't going to happen. If just the view is sufficient, then rename the table and create the view with the same name as the table. Now, you are going to have to thoroughly test insert/update/deletes to make sure they are going to work as you expect, as doing those against views isn't always what you hope it will do. You may need to create instead of triggers on the views.
Can you use DBMS_REDEFINITION to do an online table redefinition?
Good that i see thoughts are going on other options for your requirement. But anyway, in my view this is not a single question or there is no single answer for this, you need to treat it like a project and take it forward with all the diligence otherwise there are high chances that the ball may get dropped because you requirement is like plugging in into the new system which needs in & out knowledge of the existing systems and its tricks/traps if any.
>>Please explain me clearly regarding the usage of view here.
You cannot create a new object with the same name. You would need to rename the original table and create a view with the same name as the original table.
I like the suggetion by sdstuber above and use a virtual column if you are on 11g. This way they can select the column as a number or the formatted column as a varchar2.
Any way you go something needs to change in the app. Typically you store it as a number and use TO_CHAR with the correct formatting at query time or format it from the app itself at display time.
You cannot create a new object with the same name. You would need to rename the original table and create a view with the same name as the original table.
I like the suggetion by sdstuber above and use a virtual column if you are on 11g. This way they can select the column as a number or the formatted column as a varchar2.
Any way you go something needs to change in the app. Typically you store it as a number and use TO_CHAR with the correct formatting at query time or format it from the app itself at display time.