InterSystems SQL Reference
$JUSTIFY
|
|
A function that right-aligns a value within a specified width, optionally rounding to a specified number of fractional digits.
Synopsis
$JUSTIFY(expression,width[,decimal])
$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 American-format locales, or a comma (,) for European-format 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 zero-pads.
$JUSTIFY also right-aligns 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 zero-pad.
The two-argument form of
LPAD and the two-argument form of
$JUSTIFY both right-align a string by padding it with leading spaces. These two-argument 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 three-argument form of
LPAD allows you to left pad with characters other than spaces.
The value to be right-justified, and optionally expressed as a numeric with a specified number of fractional digits.
-
-
If numeric justification is desired, specify
decimal. If
decimal is specified, InterSystems IRIS 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), InterSystems IRIS 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 InterSystems IRIS converts a numeric to a canonical number, and InterSystems IRIS handling of a numeric string containing nonnumeric characters, refer to the
Numbers section of the
“Data Types and Values” chapter of
Using ObjectScript.
After
$JUSTIFY receives
expression as a canonical number,
$JUSTIFY performs its operation and either rounds or zero-pads this canonical number to
decimal number of fractional digits, then right-justifies the result, as described in
width.
The
width in which to right-justify the converted
expression. If
width is greater than the length of
expression (after numeric and fractional digit conversion), InterSystems IRIS right-justifies to width, left-padding as needed with blank spaces. If
width is less than the length of
expression (after numeric and fractional digit conversion), InterSystems IRIS 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 InterSystems IRIS sets
width to the length of the
expression value.
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 following Dynamic SQL example performs right-justification on strings. No numeric conversion is performed:
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 right-justification with a specified number of fractional digits:
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 right-justification with a specified number of fractional digits, and string right-justification 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 right-justification with the
$DOUBLE values INF and NAN:
DO ##class(%SYSTEM.Process).IEEEError(0)
SET x=$DOUBLE(1.2e500)
SET y=x-x
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()