Numeric SQL Functions
|
Numeric Function
|
Native SQLite
|
Description
|
|
ABS( number )
|
|
Returns the absolute value of the specified number.
|
|
ACOS( cosine )
|
|
Returns the angle in radians for the specified cosine.
|
|
ASIN( sin )
|
|
Returns the angle in radians for the specified sine.
|
|
ATAN( tangent )
|
|
Returns the angle in radians for the specified tangent.
|
|
ATAN2( x, y )
|
|
Two-argument arctangent function.
|
|
CEILING( number )
CEIL( number )
|
|
Returns the smallest integer larger than the specified number.
|
|
COS( radians )
|
|
Returns the cosine of the specified angle in radians.
|
|
COT( radians )
|
|
Returns the cotangent of the specified angle in radians.
|
|
DEGREES( radians )
|
|
Converts an angle in radians to an angle in degrees.
|
|
EXP( number )
|
|
Returns the constant e raised to the specified power.
|
|
FLOOR( number )
|
|
Returns the largest integer smaller than the specified number.
|
|
LN( number )
LOG( number )
|
|
Returns the natural logarithm of the specified number.
|
|
LOG10( number )
|
|
Returns the common logarithm of the specified number.
|
|
MAX( n1, n2, ... )
|
Yes
|
Returns the largest of the specified numbers. A minimum of two numbers must be specified.
|
|
MIN( n1, n2, ... )
|
Yes
|
Returns the smallest of the specified numbers. A minimum of two numbers must be specified.
|
|
MOD( dividend, divisor )
|
|
Returns the remainder when dividend is divided by divisor. Floating-point values are truncated to integers before the modulus operation is performed.
|
|
PI()
|
|
Returns the value of the constant pi (π).
|
|
POWER( number, power )
POW( number, power )
|
|
Raises number to the specified power.
|
|
RADIANS( degrees )
|
|
Converts an angle in degrees to an angle in radians.
|
|
RANDOM()
RANDOM( max )
RANDOM( min, max )
|
|
Returns a random number. RANDOM() returns a number between 0 and 1. RANDOM( max ) returns a number between 0 and max. RANDOM( min, max ) returns a number between min and max. This function is equivalent to the Random Uniform() JSL function, and its seed can be controlled using the Random Reset() JSL function. RANDOM can be shortened to RAND.
|
|
RANDOMBLOB( length )
|
Yes
|
Returns an N-byte blob that contains pseudo-random bytes. See the SQLite Online documentation at https://www.sqlite.org/lang.html for details.
|
|
ROUND( number, <precision> )
|
|
Rounds number to the number of decimal places given by precision. The default value of precision is 0, and precision can be negative.
|
|
SIGN( number )
|
|
Returns 1 if number is positive, -1 if number is negative, or 0 if number is zero.
|
|
SIN( radians )
|
|
Returns the sin of the specified angle in radians.
|
|
SQRT( number )
|
|
Returns the square root of number.
|
|
TAN( radians )
|
|
Returns the tangent of the specified angle in radians.
|
|
TRUNCATE( number, <precision> )
|
|
Truncates number at the number of decimal places given by precision. The default value of precision is 0, and precision can be negative. TRUNCATE() can be shortened to TRUNC().
|
Date-Time SQL Functions
Using date-time functions in JMP queries is complicated by the fact that the SQL engine that handles JMP queries (SQLite) uses different formats for storing dates than JMP does. SQLite stores date-times as strings. However, JMP stores date-times as the number of seconds since January 1, 1904. When you have columns in your table that contain date-times, the conversions are handled automatically. However, when you use functions that return date-times, you might need to let JMP know when a conversion is required.
Consider the CURRENT_TIMESTAMP function. CURRENT_TIMESTAMP is a built-in SQLite function that returns the current UTC/GMT time stamp as a SQLite time string:
Query( Scalar, "SELECT CURRENT_TIMESTAMP;" );
returns:
"2016-02-16 15:44:42"
The string could perhaps be parsed as a date to return it as a JMP date. To prevent the need to do so, wrap the CURRENT_TIMESTAMP function in the JMPDATE() function:
Query( Scalar, "SELECT JMPDATE( CURRENT_TIMESTAMP );" );
returns:
3538482531
The string is an unformatted JMP date. However, if you pass a SQLite time string to another SQL date-time function, you do not need to use JMPDate(); the value will be converted to a JMP date automatically. Here is an example:
Query( Scalar, "SELECT EXTRACT(’YEAR’, CURRENT_TIMESTAMP);" );
Using native SQLite date-time functions (date(), time(), datetime(), julianday(), strftime()) in JMP queries is not recommended because JMP date-time values are not compatible with those functions.
|
Date-Time Function
|
Naive SQLite
|
Description
|
|
|
CURRENT_DATE
|
Yes
|
Returns the current date (UTC/GMT) as a SQLite time string.
|
|
|
CURRENT_TIME
|
Yes
|
Returns the current time (UTC/GMT) as a SQLite time string.
|
|
|
CURRENT_TIMESTAMP
|
Yes
|
Returns the current date and time (UTC/GMT) as a SQLite time string.
|
|
|
DATEDIFF( date1, date2, interval, <alignment = “Start”> )
|
|
Computes the difference between two dates in units specified by interval, based on alignment. This function works the same as the Date Difference() JSL function. Valid values for interval are: “Year”, “Quarter”, “Month”, “Week”, “Day”, “Hour”, “Minute” and “Second”. Valid values for alignment are “Start”, “Actual” and “Fractional”. If alignment is not specified, “Start” is used.
|
|
|
EXTRACT( datepart, datetime, <use_locale = 1> )
|
|
Extracts a specific part of a date or date-time value. Datetime is a JMP date-time value or a SQLite time string. Use_locale is optional and applies only to date name parts such as "MonthName" and "DayName" and determines whether values from the current language or English are returned. The following values of datepart are supported:
|
|
|
"Year"
|
Returns the year as a number.
|
||
|
"Month"
|
Returns the numeric month (1-12).
|
||
|
"MonthName"
|
Returns the full name of the month in the current language (use_locale = 1) or English (use_locale = 0).
|
||
|
"Mon", "MMM"
|
Returns the abbreviated name of the month.
|
||
|
"Day"
|
Returns the day of the month (1-31).
|
||
|
"DayName"
|
Returns the name of the day of the week.
|
||
|
"DayOfWeek"
|
Returns the numeric day of the week (1-7).
|
||
|
"DayOfYear"
|
Returns the numeric day of the year (1-366).
|
||
|
"Quarter"
|
Returns the numeric quarter (1-4).
|
||
|
"Hour"
|
Returns the hour (0-23).
|
||
|
"Minute"
|
Returns the minute (0-59).
|
||
|
"Second"
|
Returns the seconds, including any fractional part.
|
||
|
"Date"
|
Returns just the date portion of a date-time value as a JMP date-time value.
|
||
|
"Time"
|
Returns just the time portion of a date-time value as a JMP date-time value.
|
||
|
JMPDATE( SQLite time string )
|
|
Converts a SQLite time string to the equivalent JMP date-time value.
|
|
|
NOW()
|
|
A synonym for TODAY().
|
|
|
TODAY()
|
|
Returns the JMP date-time value of the current moment in local time, which matches the JMP Today() function.
|
|
String SQL Functions
|
Function
|
Native SQLite
|
Description
|
|
HEX( binary )
|
Yes
|
SQLite built-in function that converts a BLOB to a string of hexadecimal characters. Useful when paired with the RANDOMBLOB() function.
|
|
JLEFT( string, len, <pad> )
|
|
Like the JSL Left() function. Returns len characters from the beginning of string. If pad is specified and fewer than len characters are present in string, the result is padded with pad out to length len.
|
|
JRIGHT( string, len, <pad> )
|
|
Like the JSL Right() function. Returns len characters from the end of string. If pad is specified and fewer than len characters are present in string, the result is padded with pad at the front out to length len.
|
|
LENGTH( string )
|
Yes
|
SQLite equivalent of the ANSI standard CHAR_LENGTH() function. Returns the length of its string argument in characters.
|
|
LOCATE( string1, string2 )
POSITION( string1, string2 )
|
|
Returns the (1-based) starting position of string1 within string2, returning 0 if string1 is not found within string2.
|
|
LOWER( string )
|
|
Returns a copy of string with all uppercase characters converted to lowercase.
|
|
LTRIM( string, <trimchars> )
|
Yes
|
Trims any characters contained in trimchars from the beginning of string and returns the result. If trimchars is omitted, spaces are trimmed.
|
|
PRINTF( format, <arg1, ..., argN> )
|
Yes
|
Allows constructing strings using placeholders and arguments. See the SQLite Online documentation at https://www.sqlite.org/lang.html for details.
|
|
REPLACE( string, find, replace )
|
Yes
|
Replaces all instances of find in string with replace and returns the result. If replace is numeric, it is converted to a string.
|
|
REVERSE( string )
|
|
Returns a copy of string with the order of the characters reversed.
|
|
RTRIM( string, <trimchars> )
|
Yes
|
Trims any characters contained in trimchars from the end of string and returns the result. If trimchars is omitted, spaces are trimmed.
|
|
SPACE( length )
|
|
Returns a string consisting of length space characters.
|
|
SUBSTR( string, start, <length> )
|
Yes
|
Returns the substring of string starting at start (1-based) that is length characters long. If length is omitted, the substring starting at start and continuing to the end of string is returned.
|
|
TRIM( string, <trimchars> )
|
|
Trims any characters contained in trimchars from the end of string and returns the result. If trimchars is omitted, spaces are trimmed.
|
|
UPPER( string )
|
|
Returns a copy of string with all lowercase characters converted to uppercase.
|
System SQL Functions
|
Function
|
SQLite
|
Description
|
|
COALESCE( arg1,..., argN )
|
Yes
|
Returns the first argument passed to it that is non-NULL. Returns NULL if all arguments are NULL. Requires at least two arguments.
|
|
IFNULL( arg1, arg2 )
|
Yes
|
Returns arg1 if not NULL, otherwise arg2. Basically, IFNULL is a two-argument version of COALESCE().
|
|
NULLIF( arg1, arg2 )
|
Yes
|
Returns arg1 if arg1 and arg2 are different and returns NULL if the arguments are equal. Used when you have non-NULL values in your database that you want to treat as NULL.
|
Aggregate SQL Functions
When passing a single argument to an aggregate function, that argument can be preceded by the keyword DISTINCT, which filters out duplicate values.
For all aggregations other than COUNT( * ), NULL and missing values are ignored.
|
Function
|
SQLite
|
Description
|
|
AVG( num_expr )
|
|
Computes the average of num_expr for the rows in the group. Num_expr must be numeric.
|
|
COUNT( expr )
COUNT( * )
|
|
Counts the number of times expr is not NULL in the group. COUNT( * ) returns the total number of rows in the group.
|
|
GROUP_CONCAT( expr, <separator = ’,’> )
|
Yes
|
Concatenates all non-NULL values of expr and returns them as a string. Numeric values of expr are converted to character. If separator is present, it is placed between the values. The default separator is a comma. DISTINCT can be used only with GROUP_CONCAT() if separator is not specified.
|
|
MAX( expr )
|
|
Returns the maximum value of expr in the group. Expr can be character or numeric.
|
|
MIN( expr )
|
|
Returns the minimum value of expr in the group. Expr can be character or numeric.
|
|
STDDEV_POP( num_expr )
|
|
Computes the population standard deviation of num_expr for the group.
|
|
STDDEV_SAMP( num_expr )
|
|
Computes the sample standard deviation of all num_expr for the group.
|
|
SUM( num_expr )
|
|
Returns the sum of num_expr for the group. If no non-NULL values are found, SUM() returns NULL.
|
|
TOTAL( num_expr )
|
Yes
|
Same as SUM( num_expr ), except TOTAL() returns 0.0 if no non-NULL values are found.
|
|
VAR_POP( num_expr )
|
|
Computes the population variance of num_expr for the group.
|
|
VAR_SAMP( num_expr )
|
|
Computes the sample variance of num_expr for the group.
|
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.