Caché SQL Reference
$JUSTIFY
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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])
Arguments
expression The value that is to be right-aligned. 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 right-aligned. 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 right-aligned within the specified width. You can include the decimal argument to decimal-align numbers within width.
$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")
 
Commonly, $JUSTIFY is used to format numbers with fractional digits: every number is given the same number of fractional digits, and the numbers are right-aligned so that the DecimalSeparator characters align in a column of numbers.
$JUSTIFY and LPAD
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.
Arguments
expression
The value to be right-justified, and optionally expressed as a numeric with a specified number of fractional digits.
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), Caché 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), 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 right-justification 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",$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:
  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",$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",$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",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(x,y)
  DO rset.%Display()
 
See Also