Converting
Signed Integers to Right Justified Character Strings
Question
In Rdb V6.0, I need to convert an INT(2) (signed, scaled integer) to a right-justified, zero-filled character string so that I can move the data to a non-Rdb, non-VMS environment. CAST(field AS CHAR(12)) returns a left-justified string with a leading minus sign. Is there any way to accomplish the transformation I need?
Answer
Yes, there are actually several ways to accomplish this. One way would be to define your own formatting function as an external function. This involves writing code and building a shareable image..
However, there is another, somewhat contorted way to do this that only requires defining an external function to utilize one of the VMS Run Time Library procedures.
The primary requirement for accomplishing this is to be able to find the position of the first blank, and the position of the negative sign if it exists.
The following example defines the function str_position to call the STR$POSITION RTL routine. (See "HELP RTL STR$ STR$POSITION" for a description of the parameters.) Note that Rdb V6.1 includes a POSITION function, so this external function definition will not be needed in V6.1.
It then sets some of the salary_amounts in the salary_history table of the sample personnel database to negative to illustrate how this handles negative numbers.
The Select statement selects the salary_amount field twice. The second version is processed to produce the right-justified, leading zero version of the field. This field is bracketed with angle brackets (<>) to show the boundries of the field. This could be included in a view definition that could be unloaded for transfer to some other system.
Example:
Example Source:
The source for this example is:
Copyright © 1995, JCC Consulting, Inc. All rights reserved. Proprietary to JCC Consulting, Inc. Information retrieved from this server may be used for internal purposes only and may not be posted elsewhere without written permission. Information accessible from this server is provided on an as-is basis. JCC exercises no editorial control over the information provided, except that written by JCC dealing with JCC services and products. JCC and the JCC logo are trademarks of JCC Consulting, Inc. The look of the signs or header panels is a trademark of JCC Consulting, Inc. Oracle is a registered trademark of Oracle Corporation. ORACLE Rdb is a trademarks of Oracle Corporation.
