$JUSTIFY (SQL)
Synopsis
$JUSTIFY(expression,width[,decimal])
Arguments
Argument  Description 

expression  The value that is to be rightaligned. It can be a numeric value, a string literal, or an expression that resolves to a numeric or string. 
width  The number of characters within which expression is to be rightaligned. A positive integer or an expression that evaluates to a positive integer. 
decimal  Optional — The number of fractional digits. A positive integer or an expression that evaluates to a positive integer. Caché rounds or pads the number of fractional digits in expression to this value. If you specify decimal, Caché treats expression as a numeric. 
Description
$JUSTIFY returns the value specified by expression rightaligned within the specified width. You can include the decimal argument to decimalalign numbers within width.

$JUSTIFY(expression,width): the 2argument syntax rightjustifies expression within width. It does not perform any conversion of expression. The expression can be a numeric or a nonnumeric string.

$JUSTIFY(expression,width,decimal): the 3argument syntax converts expression to a canonical number, rounds or zero pads fractional digits to decimal, then rightjustifies the resulting numeric value within width. If expression is a nonnumeric string or NULL, Caché converts it to 0, pads it, then rightjustifies it.
SQLCODE 380 is issued if you specify too few arguments. SQLCODE 381 is issued if you specify too many arguments.
$JUSTIFY recognizes the DecimalSeparator character for the current locale. It adds or deletes a DecimalSeparator character as needed. The DecimalSeparator character depends upon the locale; commonly it is either a period (.) for Americanformat locales, or a comma (,) for Europeanformat locales. To determine the DecimalSeparator character for your locale, invoke the following method:
WRITE ##class(%SYS.NLS.Format).GetFormatItem("DecimalSeparator")
$JUSTIFY, ROUND, and TRUNCATE
When rounding to a fixed number of fractional digits is important — for example, when representing monetary amounts — use $JUSTIFY, which returns the specified number of trailing zeros following the rounding operation. When decimal is larger than the number of fractional digits in expression, $JUSTIFY zeropads. $JUSTIFY also rightaligns the numbers, so that the DecimalSeparator characters align in a column of numbers.
ROUND also rounds to a specified number of fractional digits, but its return value is always normalized, removing trailing zeros. For example, ROUND(10.004,2) returns 10, not 10.00. Unlike $JUSTIFY, ROUND allows you to specify either rounding (the default), or truncation.
TRUNCATE truncates to a specified number of fractional digits. Unlike ROUND, if the truncation results in trailing zeros, these trailing zeros are preserved. However, unlike $JUSTIFY, TRUNCATE does not zeropad.
ROUND and TRUNCATE allow you to round (or truncate) to the left of the decimal separator. For example, ROUND(128.5,1) returns 130.
$JUSTIFY and LPAD
The twoargument form of LPAD and the twoargument form of $JUSTIFY both rightalign a string by padding it with leading spaces. These twoargument forms differ in how they handle an output width that is shorter than the length of the input expression: LPAD truncates the input string to fit the specified output length. $JUSTIFY expands the output length to fit the input string. This is shown in the following example:
SELECT '>'LPAD(12345,10)'<' AS lpadplus, '>'$JUSTIFY(12345,10)'<' AS justifyplus, '>'LPAD(12345,3)'<' AS lpadminus, '>'$JUSTIFY(12345,3)'<' AS justifyminus
The threeargument form of LPAD allows you to left pad with characters other than spaces.
Arguments
expression
The value to be rightjustified, and optionally expressed as a numeric with a specified number of fractional digits.

If string justification is desired, do not specify decimal. The expression can contain any characters. $JUSTIFY rightjustifies expression, as described in width.

If numeric justification is desired, specify decimal. If decimal is specified, Caché supplies expression to $JUSTIFY as a canonical number. It resolves leading plus and minus signs and removes leading and trailing zeros. It truncates expression at the first nonnumeric character. If expression begins with a nonnumeric character (such as a currency symbol), Caché converts the expression value to 0. Canonical conversion does not recognize NumericGroupSeparator characters, currency symbols, multiple DecimalSeparator characters, or trailing plus or minus signs. For further details on how Caché converts a numeric to a canonical number, and Caché handling of a numeric string containing nonnumeric characters, refer to the Numbers section of the “Data Types and Values” chapter of Using Caché ObjectScript.
After $JUSTIFY receives expression as a canonical number, $JUSTIFY performs its operation and either rounds or zeropads this canonical number to decimal number of fractional digits, then rightjustifies the result, as described in width.
width
The width in which to rightjustify the converted expression. If width is greater than the length of expression (after numeric and fractional digit conversion), Caché rightjustifies to width, leftpadding as needed with blank spaces. If width is less than the length of expression (after numeric and fractional digit conversion), Caché sets width to the length of the expression value.
Specify width as a positive integer. A width value of 0, the empty string (''), NULL, or a nonnumeric string is treated as a width of 0, which means that Caché sets width to the length of the expression value.
decimal
The number of fractional digits. If expression contains more fractional digits, $JUSTIFY rounds the fractional portion to this number of fractional digits. If expression contains fewer fractional digits, $JUSTIFY pads the fractional portion with zeros to this number of fractional digits, adding a Decimal Separator character, if needed. If decimal=0, $JUSTIFY rounds expression to an integer value and deletes the Decimal Separator character.
If the expression value is less than 1, $JUSTIFY inserts a leading zero before the DecimalSeparator character.
The $DOUBLE values INF, INF, and NAN are returned unchanged by $JUSTIFY, regardless of the decimal value.
Examples
The following Dynamic SQL example performs rightjustification on strings. No numeric conversion is performed:
ZNSPACE "SAMPLES" SET myquery = "SELECT TOP 20 Age,$JUSTIFY(Name,18),DOB FROM Sample.Person" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"End of data"
The following Dynamic SQL example performs numeric rightjustification with a specified number of fractional digits:
ZNSPACE "SAMPLES" SET myquery = 2 SET myquery(1) = "SELECT TOP 20 $JUSTIFY(Salary,10,2) AS FullSalary," SET myquery(2) = "$JUSTIFY(Salary/7,10,2) AS SeventhSalary FROM Sample.Employee" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display() WRITE !,"End of data"
The following Dynamic SQL example performs numeric rightjustification with a specified number of fractional digits, and string rightjustification of the same numeric value:
SET myquery = 2 SET myquery(1) = "SELECT $JUSTIFY({fn ACOS(1)},8,3) AS ArcCos3," SET myquery(2) = "$JUSTIFY({fn ACOS(1)},8) AS ArcCosAll" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute() DO rset.%Display()
The following Dynamic SQL example performs numeric rightjustification with the $DOUBLE values INF and NAN:
DO ##class(%SYSTEM.Process).IEEEError(0) SET x=$DOUBLE(1.2e500) SET y=xx SET myquery = 2 SET myquery(1) = "SELECT $JUSTIFY(?,12,2) AS INFtest," SET myquery(2) = "$JUSTIFY(?,12,2) AS NANtest" SET tStatement = ##class(%SQL.Statement).%New() SET qStatus = tStatement.%Prepare(.myquery) IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset = tStatement.%Execute(x,y) DO rset.%Display()