SQL MODULUS

<< Click to Display Table of Contents >>

Navigation:  Notes on Local SQL >

SQL MODULUS

 Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

SELECT statement

CAST function

Column correlation names

MODULUS

The MODULUS function is not implemented in the ISO SQL-92 language and is only variably available in different flavours throughout various SQL implementations.
To obtain a generally functional equivalent of

X MODULUS Y

this can be done by the following statement:

SELECT (X - ((CAST(X AS INTEGER) / Y) * Y)) AS "Derived Field"

where the output for Local SQL has to reference a specific table even though the expression may derive from some other table or even more tables:

SELECT (Table1.X - ((CAST(Table1.X AS INTEGER) / Y) * Y)) AS Table1."Derived Field"

The object is to turn the X into an integer data type and thus functionally turning the interim division into an integer division truncating the decimals off the result.
The Table1.X may be af field reference, a calculated expression on one or more fields or some constant.

 

Supplementary notes on LocalSQL added by Niels Knabe