Caché SQL Reference
%MINUS


A collation function that converts numbers to canonical collation format, then inverts the sign.
Synopsis
%MINUS(expression)
%MINUS expression
%MINUS converts numbers or numeric strings to canonical form, inverts the sign, then returns these
expression values in numeric collation sequence.
%MINUS and
%PLUS are functionally identical, except that
%MINUS inverts the sign. It prefixes a minus sign to any number that resolves to a positive number, and removes the minus sign from any number that resolves to a negative number. Zero is never signed.
A number can contain leading and trailing zeros, multiple leading plus and minus signs, a single decimal point indicator (.), and the E exponent indicator. In canonical form, all arithmetic operations are performed, exponents are expanded, signs are resolved to either a single leading minus sign or no sign, and leading and trailing zeros are stripped.
A numeric literal can be specified with or without enclosing string delimiters. If a string contains nonnumeric characters,
%MINUS truncates the number at the first nonnumeric character, and returns the numeric part in canonical form. A nonnumeric string (any string that begins with a nonnumeric character) is returned as 0.
%MINUS also returns NULLs as 0.
%MINUS is a Caché SQL extension and is intended for SQL lookup queries.
You can perform the same collation conversion in ObjectScript using the
Collation() method of the
%SYSTEM.Util class:
WRITE $SYSTEM.Util.Collation("++007.500",4)
Compare
%MINUS to
%MVR collation, which sorts a string based on the numeric substrings within the string.
The following example uses
%MINUS to return records in descending numeric order of the home street number:
SELECT Name,Home_Street
FROM Sample.Person
ORDER BY %MINUS(Home_Street)
Note that the above example orders the integer part of the street address in numerical order. Compare this with the following ORDER BY DESC example, which orders records by street addresses in collation sequence:
SELECT Name,Home_Street
FROM Sample.Person
ORDER BY Home_Street DESC