Re: [firebird-support] Raising the scale of a NUMERIC field
A very interesting post, I had learned something new. Greetings. Walter. On Mon, Oct 29, 2012 at 2:48 PM, Mark Rotteveel m...@lawinegevaar.nlwrote: ** On 29-10-2012 17:48, Carlos H. Cantu wrote: MR No, a direct system table update like that would rescale all your existing MR numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become MR 5.43211234 Not really. You can test by yourself... seems that stored values are associated with a record in RDB$FORMATS that describes the original format when they were stored, and when FB retrieves the value, it respects the original format (thanks Ivan for the note). My tests showed no problem with my proposed approach. I posted here just to make sure that it would not have any side effects that I didnt see yet. If someone knows any, please post here. Ok, good to know that. I didn't know that the formats were also maintained when not using DDL. Mark -- Mark Rotteveel [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Raising the scale of a NUMERIC field
I need to change the scale of numeric domain from (15,4) to (15,8). I cannot use the ALTER command, since FB doesn't allow such change. BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$'; []s Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br
Re: [firebird-support] Raising the scale of a NUMERIC field
On Mon, 29 Oct 2012 13:32:16 -0200, Carlos H. Cantu lis...@warmboot.com.br wrote: I need to change the scale of numeric domain from (15,4) to (15,8). I cannot use the ALTER command, since FB doesn't allow such change. BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$'; No, a direct system table update like that would rescale all your existing numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become 5.43211234 Firebird stores NUMERIC (and DECIMAL) as an INT (or BIGINT or SMALLINT) and the scale is used to decide the position of the decimal. Mark
Re: [firebird-support] Raising the scale of a NUMERIC field
MR No, a direct system table update like that would rescale all your existing MR numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become MR 5.43211234 Not really. You can test by yourself... seems that stored values are associated with a record in RDB$FORMATS that describes the original format when they were stored, and when FB retrieves the value, it respects the original format (thanks Ivan for the note). My tests showed no problem with my proposed approach. I posted here just to make sure that it would not have any side effects that I didnt see yet. If someone knows any, please post here. []s Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br MR On Mon, 29 Oct 2012 13:32:16 -0200, Carlos H. Cantu MR lis...@warmboot.com.br wrote: I need to change the scale of numeric domain from (15,4) to (15,8). I cannot use the ALTER command, since FB doesn't allow such change. BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$';
Re: [firebird-support] Raising the scale of a NUMERIC field
On Mon, Oct 29, 2012 at 12:20 PM, Mark Rotteveel m...@lawinegevaar.nlwrote: BUT I'm 100% sure that all the existing data would perfectly fit in (15,8) so, the question is: In this case, is it safe to make the change direct in the system table? update RDB$FIELDS set RDB$FIELD_SCALE = -8 where RDB$FIELD_NAME = 'RDB$'; No, a direct system table update like that would rescale all your existing numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become 5.43211234 Firebird stores NUMERIC (and DECIMAL) as an INT (or BIGINT or SMALLINT) and the scale is used to decide the position of the decimal. Ah, you've missed the magic of formats. Every record version is stored with a format version number. Every time a table changes shape - meaning a column is added, dropped, or altered in length or scale, Firebird stores a new format version for the table with the new physical description. A transaction asks for a record in a particular format. Firebird does what is necessary to convert the format it finds to the requested format. On update, a the record is stored in the newest format. So 54321.1234 would become 54321.1234. The new format is created when a change to the system tables is committed. In V3.0, your mileage will change. Cheers, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Raising the scale of a NUMERIC field
On 29-10-2012 17:48, Carlos H. Cantu wrote: MR No, a direct system table update like that would rescale all your existing MR numbers. Eg from (15,4) to (15,8) a value 54321.1234 would become MR 5.43211234 Not really. You can test by yourself... seems that stored values are associated with a record in RDB$FORMATS that describes the original format when they were stored, and when FB retrieves the value, it respects the original format (thanks Ivan for the note). My tests showed no problem with my proposed approach. I posted here just to make sure that it would not have any side effects that I didnt see yet. If someone knows any, please post here. Ok, good to know that. I didn't know that the formats were also maintained when not using DDL. Mark -- Mark Rotteveel
