Supported SQL

User can provide SQL SELECT queries in any of the below given Workspace SQL dialects. Follow the links to know more about the SQL statements and functions, in each given Workspace dialect, supported by Zoho Analytics CloudSQL implementation.

• ANSI SQL
• Oracle
• SQL Server
• MySQL
• DB2
• Sybase
• Informix
• PostgreSQL

Zoho Analytics supports following SELECT query syntax and in-built functions in CloudSQL.

SUPPORTED SELECT QUERY SYNTAX:

Following are the conventions used in the SELECT syntax definition:

SELECT Syntax:

SELECT [ DISTINCT | ALL ] ( ( column_clause ( "," column_clause )* ) | "*" ) [ FROM table_expression_clause ][ where_clause ] [ group_by_clause ] [ having_clause ] [ order_by_clause ] [ limit_clause ]

column_clause :

( expression | table_name.* | table_name.columnname | table_name.column_name | column_name ) [ [ AS ] alias_name ]

table_expression_clause :

( table_name ) [ AS alias_name ] ( "," ( table_name ) [ AS alias_name ])*

where_clause :

WHERE (<Left Where Expression>(= | < | > | <= | >= | != | LIKE | NOT LIKE | BETWEEN) <Right Where Expression>) ( (OPERATOR ) (<Left Where Expression> (= | < | > | <= | >= | != | LIKE | NOT LIKE | BETWEEN) <Right Where Expression>)*
OPERATOR includes AND/OR

group_by_clause :

GROUP BY expression ( "," expression )*

having_clause :

HAVING condition

order_by_clause :

ORDER BY (order_item_clause) ( "," order_item_clause )*

limit_clause :

LIMIT (value | ALL) [ "," value ]

join_clause :

table_name INNER JOIN table_name ON join_condition | <br> table_name {LEFT | RIGHT} [OUTER] JOIN table_name ON join_condition

order_item_clause :

(expression | column name) [ ASC | DESC ]

UNION Syntax:

<SELECT Query Statement> (UNION [ALL | DISTINCT] <SELECT Query Statement>)+

Reference: MySQL SELECT Query syntax documentation

MySQL STRING FUNCTIONS

The MySQL in-built String functions supported by Zoho Analytics.

ASCII(string_arg):

Purpose:

The ASCII value of the first character of the given argument is returned.

Example:

Select ascii('ant’) returns '97’

Note:

• Returns '0’ if argument is null.
• The first character of the string can be any alphabet, number, symbol or special character.

BIN(numeric_arg):

Purpose:

Returns the binary value of the given argument.

Example:

Select BIN('40’) returns '101000’

Note:

• Returns null if argument is null.
• Returns 0 if argument is any non-numeric character.

BIT_LENGTH(string_arg):

Purpose:

Returns the value of the length of the string argument in bits.

Example:

Select BIT_LENGTH('AA’) returns '16’

Note:

• The argument can consist of any numeric,alphabetic or special character.
• Returns 0 if argument is null.

CHAR(numeric_arg1, numeric_arg2, numeric_arg3,….):

Purpose:

Returns a String of characters formed from the code values of the ASCII code given in the arguments.

Example:

Select CHAR (97.4,97.5) returns 'ab’

Select CHAR ('97’,'X’,'98’) returns 'a b’ // Returns space for each non-numeric character in the argument.

Note:

• Returns null if argument is null.
• If the given ASCII is in decimals, then it returns the value of the closest whole number.
• If the given ASCII is in decimals but is given as a string then the number after the decimal point is ignored and the code value of the number before the decimal alone is returned.

CHAR_LENGTH(string_arg):

Purpose:

Returns the number of characters present in the argument.

Example:

Select CHAR_LENGTH('aa1’) returns '3’

Note:

• The argument can consist of any numeric,alphabetic or special characters.
• Returns 0 if argument is null.
• Multibyte characters are counted as a single character.

CHARACTER_LENGTH(string_arg):

Purpose:

Returns the number of characters present in the argument.

Example:

Select CHARACTER_LENGTH('aa1’) returns '3’

Note:

• The argument can consist of any numeric,alphabetic or special characters.
• Returns 0 if argument is null.
• Multibyte characters are counted as a single character.

CONCAT(string_arg1, string_arg2, string_arg3,…):

Purpose:

Returns the concatenated string of the arguments.

Example:

Select CONCAT('red’,'rose’) returns ’ redrose’

Note:

• The arguments can consist of any number,alphabet or special characters.
• Returns null if all arguments are null.
• Returns the non-null argument string if atleast one argument is not null and the rest of the arguments are null..

CONCAT_WS(char_arg, string_arg1, string_arg2,…):

Purpose:

Returns the concatenated string of the arguments with the first argument acting as the separator between the concatenated strings.

Example:
Select CONCAT_WS (’,’,'red’,'rose’,'is’,'beautiful’) returns 'red,rose,is,beautiful’

Note:

• The arguments can consist of any number,alphabet or special characters.
• Returns null if all arguments are null.
• The separator can be any symbol, letter, string, or number.
• If the first argument is null, Returns the concatenated string of the rest of the arguments .
• If the first argument is non-null and the rest of the arguments are null then the separator is returned concatenated with itself (n-1) times, if 'n’ is the number of arguments.

ELT(numeric_arg, string_arg1, string_arg2,…):

Purpose:

The value of the first argument gives the count of the argument to be returned from the rest of the arguments.

Example:

Select ELT ('2’,'red’,'rose’,'is’,'beautiful’) returns 'rose’

Note:

• The count of the argument to be returned is started from the second argument.
• Returns null if first argument is null or the number of arguments is less than the count.
• If the first argument is a non numeric character, then null is returned.

FIELD(string_arg, string_arg1, string_arg2,…):

Purpose:

The first argument string is checked with all the other arguments and the position of the argument where the first match is found is returned.

Example:

Select FIELD ('as’,'has’,'as’,'have’) returns '2’

Note:

• The position of the arguments starts from the second argument.
• If the first argument or the rest of the arguments are null then '0’ is returned.
• Returns '0’ if no match is found.

FIND_IN_SET(string_arg1, string(element1,element2,…)):

Purpose:

The first argument string is checked with the second argument, which is a set of strings separated by commas, and the position of the first match in the set is returned.

Example:

Select FIND_IN_SET ('10’,'2,5,8,10’) returns '4’

Note:

• If either the first or the second argument is null, then '0’ is returned.
• If both the first and the second argument are null, then 'null’ is returned.

FORMAT(numeric_arg,numeric_arg):

Purpose:

Rounds off the number given in the first argument to the number of decimals given in the second argument.

Example:

SELECT FORMAT (1.0001111,5) returns '1.00011’

Note:

• If the second argument is 0 then there are no decimal value or decimal point in the returned number.
• If the number has no decimals then zeros are appended to the number after the decimal point.

HEX(string_arg):

Purpose:

Returns the corresponding hexadecimal number for each character of the given string.

Example:

Select HEX('255’) returns '323535’

Note:

• If the given argument is null then null is returned.
• If the given argument is given as a number then the hexadecimal number of the number as a whole is returned.

INSTR(string_arg, string_arg):

Purpose:

Returns the position of the first match of second string argument in the first argument.

Example:

Select INSTR ('impossible’,'possible’) returns '3’

Note:

• If both the arguments are null then '1’ is returned.
• If the second argument alone is null then '1’ is returned.
• If the first argument alone is null then '0’ is returned.
• If the second argument does not match with the first string then '0’ is returned

LEFT(string_arg, numeric_arg):

Purpose:

From the first argument string the number of characters as that passed in the second argument is returned.

Example:

Select LEFT ('select’,'3’) returns 'sel’

Note:

• If null, '0’ or any non numeric character is passed in the second argument then null is returned.
• If the value of the second argument exceeds that of the first argument string then only the first argument string is fully returned

LENGTH(string_arg):

Purpose:

Returns the total number of bytes present in the string. The multibyte characters are counted as multiple bytes.

Example:

Select LENGTH ('advent123!$’) returns '11’

Note:

• Returns 0 if the argument is null

LOCATE(string_arg):

Purpose:

Returns the position of the first string argument in the second string argument.

Example:

Select LOCATE ('net’,'adventnet’) returns '7’

Note:

• Returns 0 if the first argument is not present in the second argument.
• Returns '0’ if the second argument is null.
• Returns 1 if the first argument is null.
• Returns 1 if both the arguments are null.

LPAD(string_arg, numeric_arg, string_arg):

Purpose:

The third argument is prepended to the left of the first argument until the length given in the second argument is attained.

Example:

Select LPAD ('brother’,'9’,'hello’) returns 'hebrother’

Note:

• If the total length of the resultant string is more than the length to be attained, then the third string argument is cut off as the required length is attained.
• Returns null if the third argument is null.
• Returns null if the second argument is '0’ or null.
• If the length of the first argument exceeds the given count then the first argument is cut off upto the required result.
• If the total length of the resultant string is less than the length to be attained, then the third string argument is repeated till the required length is attained.

LTRIM(string_arg):

Purpose:

Returns the argument with the blank spaces at the prefix of the string removed.

Example:

Select LTRIM (’ ab cd’) returns 'ab cd’

Note:

• The space between the characters are not affected.

MAKE_SET(numeric_arg, string_arg1, string_arg2,…):

Purpose:

For the given bit in the first argument, the corresponding 'set’ of arguments from the remaining arguments is returned.

Example:

Select MAKE_SET (3,'ab’,'cd’,'ef’) returns 'ab,cd’

Note:

• Returns null if the first argument is null or 0.

ORD(string_arg):

Purpose:

If the first character of the string argument is a multi-byte character, then the code calculated from the below formula is returned.

1st byte code + (2nd byte code * 256) + (3rd byte code * 256 * 256) + …

REPEAT(string_arg, numeric_arg):

Purpose:

The first string argument is returned repeatedly the number of times as that given in the second argument.

Example:

Select Repeat ('AA’,5) returns 'AAAAAAAAAA’

Note:

• Returns null if either or both the first argument and the second argument are null.

REPLACE(string_arg, string_arg, string_arg):

Purpose:

Replaces the string given in the second argument with the string given in the third argument from the first argument string.

Example:

select replace('tention’,'ten’,'celebra’) returns 'celebration’

Note:

• Returns the first argument string if the second argument is null.
• If the third argument is null, Returns the first argument string with the second argument string removed from it.
• If the second and third argument are null then the first argument string is returned.

REVERSE(string_arg):

Purpose:

Returns the string argument in the reversed order of characters.

Example:

select reverse ('main’) returns 'niam’

Note:

• Returns null if the argument is null.

RIGHT(string_arg, numeric arg):

Purpose:

For the count passed in the second argument the first argument’s characters from the right end is returned.

Example:

select RIGHT ('adventnet’,3) returns 'net’

Note:

• Returns null if either or both the first and second arguments are null.

RPAD(string_arg, numeric arg, string_arg):

Purpose:

The third argument is appended to the right of the first argument until the length of the second argument is acheived.

Example:

select RPAD ('abcdef’,10,'ghijkl’) returns 'abcdefghij’

Note:

• If the total length of the resultant string is more than the length to be attained, then the resultant argument string is cut off as soon as the required length is attained.
• Returns null if either the second or the third argument is null.
• If the total length of the resultant string is less than the length to be attained, then the third string argument is repeated till the required length is attained.
• Returns the repeated third argument if the first argument is null.

RTRIM(string_args):

Purpose:

Returns the argument with the blank spaces at the suffix of the string removed.

Example:

Select rtrim ('abcd ’) returns 'abcd’

Note:

• Returns null if the argument is null.

SOUNDEX(string_arg):

Purpose:

Returns the soundex string of the given argument. The soundex string is similar for same sounding strings.

Example:

Both Select soundex ('bye’) and Select soundex ('boy’) return 'b000’

SPACE(numeric_arg):

Purpose:

The argument value is returned as the number of space characters.

Example:

Select space(6) returns ’ ’

Note:

• Returns null for non-numeric and negative arguments.

SUBSTRING(string_arg, numeric_arg):

Purpose:

Returns the substring formed by cutting off the string argument passed according to the needs.

Example:

Select substring ('adventnet’,7) returns 'net’
Select substring ('adventnet’ From 3) returns 'ventnet’
Select substring ('adventnet’, 3,4 ) returns 'vent’
Select substring ('adventnet’, -3) returns 'net’
<> Select substring ('adventnet’, -7,4) returns 'vent’

Note:

• Returns null when the numeric argument exceeds or is less than the length of the string argument.

SUBSTRING_INDEX(string_arg, string_arg, numeric_arg):

Purpose:

The second argument acts as the delimiter and the third argument is the count number. As soon as the match of the delimiter is found for the correct count in the first argument string, the rest of the string is cut off.

Example:

select substring_index ('how.are.you’, ’.’, 2) returns 'how.are’ // for positive count, limitation is done to the right of the delimiter.

select substring_index ('how.are.you’, ’.’, -2) returns 'are.you’ // for negative count, limitation is done to the left of the delimiter

Note:

• If the count is null, zero or any non-numeric character then null is returned.
• If the count is greater than the number of delimiters present in the string then the given string is returned without any change made to it.

TRIM(string_arg):

Purpose:

For the given specifiers the string argument is trimmed accordingly.

Example:

Select TRIM (’ zoho ’) returns 'zoho’ // if no specifier is given, spaces before and after the string are trimmed

Select TRIM (leading ’!’ from ’!!!!zoho!!!!’) returns 'zoho!!!!’ // if leading specifier is given then the prefix part is trimmed

Select TRIM (trailing ’!’ from ’!!!!zoho!!!!’) returns ’!!!!zoho’ // if trailing specifier is given then the suffix part is trimmed.

UNHEX(string_arg):

Purpose:

Returns the corresponding character for each pair of hexadecimal digits.

Example:

Select unhex('21’) returns ’!’

Note:

• Returns null when the argument value is a non hexadecimal number.

UPPER(string_arg):

Purpose:

Returns the argument string with all its alphabetic characters in Upper Case.

Example:

select upper ('AdVeNt’) returns 'ADVENT’

Note:

• The numeric and symbolic characters remain unchanged.

MySQL MATHEMATICAL FUNCTIONS

The MySQL in-built Math functions supported by Zoho Analytics

ABS(numeric_arg):

Purpose:

Returns the numerical value of a number given in the argument without regard of its sign.

Example:

Select ABS (-23) returns '23’

Select ABS (’-23’) returns '23.0’ // If a number is passed as a string then the number is returned in decimal form.

Select ABS (’-23B0011’) returns '23.0’ // As soon as a non numeric character is found in the number string the number is returned in decimal form.

Note:

• Returns '0.0’ for non-numeric string arguments.
• Returns '0.0’ for null arguments.

ACOS(numeric_arg):

Purpose:

Returns the Inverse Cosine value of the argument passed. The argument’s value should be between -1 to 1.

Example:

Select ACOS (0.5) returns '1.0471975511966’

Select ACOS ('0.5A@5’) returns '1.0471975511966’ // As soon as a non numeric character is found in the number string the number value is returned.

Note:

• Returns the value of Acos(0) i.e '1.5707963267949’ for null argument.
• Returns the value of Acos(0) i.e '1.5707963267949’ for non numeric argument.
• Returns null if the numeric argument value is out of range.

ASIN(numeric_arg):

Purpose:

Returns the Inverse Sine value of the argument passed. The argument’s value shuld be between -1 to 1.

Example:

Select ASIN (0.5) returns '0.5235987755983’

Select ASIN ('0.5A@5’) returns '0.5235987755983’ // As soon as a non numeric character is found in the number string the number’s value is returned.

Note:

• Returns '0.0’ for null argument.
• Returns '0.0’ for non numeric argument.
• Returns null if the numeric argument value is out of range.

ATAN(numeric_arg):

Purpose:

Returns the Inverse Tangent value of the argument passed.

Example:

Select ATAN (0.5) returns '0.46364760900081’

Select ATAN ('0.5A@5’) returns '0.46364760900081’ // As soon as a non numeric character is found in the number string the number’s value is returned.

Note:

• Returns '0.0’ for null argument.
• Returns '0.0’ for non numeric argument.
• A negative number’s ATAN value is equal to the ATAN value of its positive number but with a minus sign.

ATAN2(numeric_arg):

Purpose:

Returns the number of characters present in the argument.

Example:

Select ATAN2 (2,4) returns '0.46364760900081’

Select ATAN2 ('0.5A’) returns '0.46364760900081’ // As soon as a non numeric character is found in the number string the number’s value is returned.

Note:

• Returns '0.0’ if one argument is null and the other argument is a non numeric number.
• Returns '0.0’ if both the arguments are null or zero.
• Returns '1.5707963267949’ when the second argument is '0’ and the first argument is any natural number.
• Returns '0.0’ when the second argument is '0’ and the first argument is any non-numeric character and vice versa.

CEIL(numeric_arg):

Purpose:

The smallest integer that is greater than or equal to the value of the argument is returned.

Example:

Select CEIL (1.5) returns '2’

Note:

• Returns '0’ if argument is null or any non-numeric character.
• As soon as a non numeric character is found in the number string argument, the CEILING of the number value is returned and the rest of the argument characters are neglected.

CEILING(numeric_arg):

Purpose:

The smallest integer that is greater than or equal to the value of the argument is returned.

Example:

Select CEILING (1.5) returns '2’

Note:

• Returns '0’ if argument is null or any non-numeric character.
• As soon as a non numeric character is found in the number string argument, the CEILING of the number value is returned and the rest of the argument characters are neglected.

CONV(string_arg, numeric_arg1, numeric_arg2):

Purpose:

Changes the argument number string from the given base to the required base. The first argument is the given number string, the second argument is the original base of the number and the third argument is the base to which the number string is to be converted.

Example:

Select CONV ('F’,16,10) returns '15’

Note:

• Returns null if any of the arguments is null.
• Returns '0’ if the bases given in the second or third arguments are non-compatible with the number.
• The maximum value of the bases is 36 and the minimum value is 2.

COS(numeric_arg):

Purpose:

Returns the COSINE value of the argument passed. The value of the arguments should be in radians.

Example:

Select COS (45) returns '0.52532198881773’

Note:

• Returns '1.0’ if the argument is null, zero or any non-numeric string.

COT(numeric_arg):

Purpose:

Returns the COTANGENT value of the argument passed.

Example:

Select COT() returns “

Note:

• Returns null if the argument is null, zero or any non-numeric string.

CRC32(numeric_arg):

Purpose:

Returns a 32 bit unsigned output after calculating the cyclic redundancy check.

Example:

Select CRC32 ('111’) returns '1298878781’

Note:

• Returns '0’ if the argument is null.
• The argument should be a string. Numbers are also taken as strings

DEGREES(numeric_arg):

Purpose:

The argument’s value is converted from radians to degrees.

Example:

Select DEGREES (3.141593) returns '180’

Note:

• Returns null if the argument is zero, null or a non-numeric string.

EXP(numeric_arg):

Purpose:

The value of 'e’ raised to the power of the given number argument is returned.

Example:

Select EXP (6) returns '403.42879349274’

Note:

• Returns '1.0’ when zero, null or any non-numeric number is passed in the argument.

FLOOR(numeric_arg):

Purpose:

The largest integer that is smaller than or equal to the value of the argument is returned.

Example:

Select FLOOR (1.5) returns '1’

Note:

• Returns '0’ if argument is null or any non-numeric character.
• As soon as a non numeric character is found in the number string argument, the FLOOR of the number value is returned and the rest of the argument characters are neglected.

FORMAT(numeric_arg, numeric_arg):

Purpose:

Rounds off the number given in the first argument to the number of decimals given in the second argument.

Example:

SELECT FORMAT (1.0001111,5) returns '1.00011’

Note:

• If the second argument is 0 then there are no decimal value or decimal point in the returned number.
• If the number has no decimals then zeros are appended to the number after the decimal point.

LN(numeric_arg):

Purpose:

Returns the natural logarithmic value of the given argument.

Example:

Select LN('123’) returns '4.8121843553724’

Note:

• Returns null if the argument is null, negative or any non-numeric character.
• As soon as a non numeric character is found in the number string argument, the LN of the number value is returned and the rest of the argument characters are neglected.

LOG(number1, number2):

Purpose:

Performs the same function as LN for single parameter. For 2 parameters, the first argument is the base and the second argument is the number. The logarithm of the number to the base is returned.

Example:

Select LOG(10,100) returns 2.0

Note:

• Only numbers are accepted. Numbers given as strings are also not accepted.

LOG2(numeric_arg):

Purpose:

The logarithm of the number to the base 2 is returned.

Example:

Select LOG2 (100) returns '6.6438561897747’

Note:

• Returns null if the argument is null, negative or any non-numeric charecter.
• Numbers given as strings are also accepted.
• As soon as a non numeric character is found in the number string argument, the LOG2 of the number is returned and the rest of the argument characters are neglected.

LOG10(numeric_arg):

Purpose:

The logarithm of the number to the base 10 is returned.

Example:

Select LOG10 (100) returns '2.0’

Note:

• Returns null if the argument is null, negative or any non-numeric character.
• Numbers given as strings are also accepted.
• As soon as a non numeric character is found in the number string argument, the LOG10 of the number is returned and the rest of the argument characters are neglected.

MOD(numeric_arg,numeric_arg):

Purpose:

The first argument is the dividend and the second argument is the divisor. The remainder of the division is returned.

Example:

Select MOD (99,8) returns '3’

Select 99 mod 8 returns '3’

Select 99 % 8 returns '3’

Note:

• Returns '0.0’ if the first argument is null and the second argument is any number.
• Returns null if the second argument is null irrespective of the first argument.

OCT(numeric_arg):

Purpose:

Returns the octal value of the number given in the argument.

Example:

Select OCT (12) returns '14’.

Note:

• Returns '0’ if the argument is null or non-numeric character string.
• As soon as a non numeric character is found in the number string argument, the OCT of the number is returned and the rest of the argument characters are neglected.

PI():

Purpose:

The value of ¶ (pi), ie 22 / 7 is returned. Only 6 numbers after the decimal point is returned by default.

Example:

Select PI () returns '3.141593’

POW(numeric_arg, numeric_arg):

Purpose:

The first argument is a number and the second argument is the power to which the given number is to be raised. The value of the number raised to the required power is returned.

Example:

Select POW (3,4) returns '81.0’

Note:

• If the second argument is zero, null or any non-numeric string then irrespective of the value and type of first argument '1.0’ is returned .

RADIANS(numeric_arg):

Purpose:

The argument’s value is converted from degree to radians.

Example:

Select RADIANS ( 180) returns '3.1415926535898’

Note:

• Returns '0.0’ for any non-numeric string argument.
• As soon as a non numeric character is found in the number string argument, the RADIANS of the number is returned and the rest of the argument characters are neglected.

RAND(numeric_arg):

Purpose:

A random value between the range of 0 and 1 is returned for the given argument.

Example:

Select RAND (3) returns '0.90576975597606’

Note:

• Returns '0.15522042769494’ if the argument is zero, null or any non-numeric string.

ROUND(numeric_arg):

Purpose:

Returns the rounded value of the given decimal number.

Example:

Select ROUND (23.248) returns '23’

Select ROUND (23.248, 1) returns '23.4’ // Rounds off the given number’s decimals upto the number given in the second argument.

Select ROUND (23.248,-1) returns '20.0’

Note:

• If the second argument is null or any non-numeric string, then the given number is rounded off assuming the value of second argument as zero.

SIGN(numeric_arg):

Purpose:

Depending on the sign of the argument, -1 is returned for negative number, 0 is returned for zero and 1 is returned for positive number.

Example:

Select SIGN (-200) returns ’-1’

Select SIGN (200) returns '1’

Select SIGN (-0) returns '0’ / returns '0’ irrespective of the sign before 0 .

Note:

• Returns '0’ if the given argument is a non-numeric string.
• If a string has numbers followed by characters then the sign of the number is returned.

SIN(numeric_arg):

Purpose:

Returns the SINE value of the argument passed. The value of the arguments should be in radians

Example:

Select SIN ( 111) returns '0.86455144861061’

Note:

• Returns '0.0’ for null and non-numeric arguments.
• As soon as a non numeric character is found in the number string the number’s SIN value is returned.

SQRT(numeric_arg):

Purpose:

Returns the square root value of the given number.

Example:

Select SQRT ('2’) returns '1.4142135623731’

Note:

• Returns null for non numeric string arguments.
• Returns '0.0’ for negative numbers.
• Returns the SQRT of the number alone when a number is followed by a non-numeric string.

TAN(numeric_arg):

Purpose:

Returns the TAN value of the argument passed. The value of the arguments should be in radians.

Example:

Select TAN ( '111’) returns '1.7203486651304’

Note:

• Returns '0.0’ for non-numeric or null arguments.
• For negative numbers, the TAN of the argument’s absolute value is found and a minus sign is added at the beginning.

TRUNCATE(string_arg):

Purpose:

The first argument is the given decimal number and the second argument is the number that decides upto which the given number’s decimals are to be truncated. The truncated number is returned.

Example:

Select TRUNCATE (234.56789,2) returns '234.56’
Select TRUNCATE (234.56789,’-2’) returns '200.0’

Note:

• Truncates all the decimals if the second argument is null, zero or any non-numeric string.

MySQL DATE FUNCTIONS

The MySQL in-built Date Time functions supported by Zoho Analytics.

ADDDATE(date_arg, numeric_arg):

Purpose:

The first argument is a date value and the second argument is the number of days to be added to it. Returns the new date.

Example:

Select ADDDATE ( ’ 2008-08-03 ’ , 20 )

Note:

• The interval to be added should be in numeric value only and should not be in the form of 'INTERVAL 31 DAYS’.

ADDTIME(time_arg, time_arg):

Purpose:

The addition value of first argument time and second argument time is returned.

Example:

Select ADDTIME ('12:30:15.55555555’, '01:00:44.444445’) returns '13:31:00.000000’

CONVERT_TZ(datetime_arg, time_arg, time_arg):

Purpose:

Converts the given time value to the required time value by adding the time given in the third argument.The resultant time value comes in 12 hour or 24 hour format depending on the second argument.

Example:
Select CONVERT_TZ ( ’ 2008-11-06 03:00:00 ’ , ’ +09:00 ’ , ’ -5:30 ’ ) returns ’ 2008-11-05 12:30:00 ’

Note:

• For the result to be in 12 hour format, the second argument should be '12:00’
• For the result to be in 24 hour format, the second argument should be '00:00’

CURDATE():

Purpose:

The present date is returned in the format of ’ yr:mth:dt ’.

Example:

Select CURDATE () returns '2008-11-06’

Note:

• Depending on the way the function is used the result is returned as a string or a number.

CURRENT_DATE():

Purpose:

Its function is the same as CURDATE()

CURRENT_TIME():

Purpose:

Its function is the same as CURTIME()

CURTIME():

Purpose:

Returns the present time of the system in the form of ’ hr : min : sec ’.

Example:

Select CURTIME() returns ’ 12 : 46 : 21 ’

Select CURTIME()+0 returns ’ 124621.0 ’

Note:

• Depending on its usage the function returns the result as a string or a number.

DATE(date.time_arg):

Purpose:

Returns the date part alone from the date-time argument given in the function.

Example:

Select DATE ( ’ 2008-08-03 03:45:00 ’ ) returns ’ 2008-08-03 ’

Note:

• Returns null if the date part is non-numeric.
• Returns the date part correctly even if the time part is non-numeric.

DATEDIFF(date.time_arg, date.time_arg):

Purpose:

Returns the difference between the 2 dates given in the arguments. For subtraction only the date part of the date-time values of both the argument is taken.

Example:

Select DATEDIFF ( '1986-08-03 03:45:22’,'1986-08-23 18:45:43’rsquo;) returns ’-20’

Note:

• The time part is not taken in the calculation. Hence even if the time part is non-numeric no difference to the result takes place.

DAYNAME(date_arg):

Purpose:

Returns the day of the week the given date is .

Example:

Select DAYNAME ( ’ 2008-11-03 ’ ) returns ’ Monday ’

Note:

• If the given date’s month and year are wrong then null is returned.
• If the given date’s value exceeds 31 then null is returned.
• The day of a month is returned even if the given date is wrong with respect to the given month. for eg: 31st february 2008 returns 'Sunday’.
• If the given date’s value is '0’ then '0’ is returned.
• This function works for the years ranging from 0 to 9999.

DAYOFMONTH(date_arg):

Purpose:

Returns the day of the month the date given in the argument is.

Example:

Select DAYOFMONTH ( ’ 2008-11-03 ’ ) returns '3’

Note:

• If the given date’s month and year are wrong then null is returned.
• If the given date’s day value exceeds 31 then null is returned.
• The date of a month is returned even if the given date is wrong with respect to the given month. for eg: 31st february returns 31.
• If the given date’s value is '0’ then '0’ is returned.
• This function works for the years ranging from 0 to 9999.

DAYOFYEAR(date_arg):

Purpose:

Returns the given date’s count from the start of the year.

Example:

Select DAYOFYEAR ( ’ 2008-12-25 ’ ) returns '360’

Note:

• Returns null if the given date and month are '0’ .
• The range of the result is between 1 and 366.

DATE_ADD(date_arg, numeric_arg):

Purpose:

Returns the given date after performing the required arithmetic on it. DATE_ADD is currently not supported by Zoho Analytics.

DATE_SUB(date_arg, numeric_arg):

Purpose:

Returns the given date after performing the required arithmetic on it. DATE_SUB is currently not supported by Zoho Analytics.

EXTRACT(command from date.time_arg):

Purpose:

Returns the required part of the date-time value after extraction from the given date-time argument.

Example:

Select EXTRACT ( HOUR_SECOND FROM ’ 2009-07-02 01:02:03 ’ ) returns ’ 10203 ’

FROM_DAYS(numeric_arg):

Purpose:

The count of days is started from the date 01-01-01. The argument value is taken as count and its corresponding date is displayed.

Example:

Select FROM_DAYS ( 733714) returns ’ 2008-11-03 ’

Note:

• The count starts from the number 366 which is the count for the date '0001-01-01’

FROM_UNIXTIME(numerical_arg):

Purpose:

For the given argument the value of the internal timestamp is returned.

Example:

Select FROM_UNIXTIME (1225741235) returns ’ 2008-11-03 19:40:35 ’

Note:

• Returns the result in number value or string value depending on the type of the given input argument.

HOUR(time_arg):

Purpose:

Returns the number of hours present in the given time value. The time is given in the form 'hr : min : sec’

Example:

Select HOUR (’ 220:22:34 ’) returns '220’

Note:

• Returns null if the value of minutes or seconds exceed '59’
• If the time is given in decimal form then '0’ is returned.

LAST_DAY(date.time_arg):

Purpose:

Returns the last date of the given date-time argument’s month.

Example:

Select LAST_DAY ( ’ 2004-02-31 01:02:03 ’ ) returns '29’

Note:

• Returns null if the given day, month or year is wrong or exceeds their range.

LOCALTIMESTAMP():

Purpose:

The present date-time values of the application’s time zone is returned. The result is returned in the form of ’ yr : mth : dt hr : min : sec ’.

Example:

Select LOCALTIMESTAMP () returns '2008-08-23 12:59:41’

Note:

• The result is returned in numeric form or string according to the function’s use in the query.

MAKEDATE(numeric_arg, numeric_arg):

Purpose:

The argument contains the year and the count of the day for that year. The date is returned.

Example:

Select MAKEDATE ( 2008,215 ) returns ’ 2008-08-03 ’

Note:

• Returns null if the count passed is '0’.
• If the count is greater than 366, then the year of the date also changes.

MICROSECOND(date.time_arg):

Purpose:

From the given date-time argument value, the microsecond term alone is returned.

Example:

Select MICROSECOND ( ’ 2008-11-04 17:16:50.123 ’ ) returns '123000’

Note:

• The default number of microsecond digits that is returned is '6’
• Returns '0’ if there is no microsecond term in the argument.

MID(date_arg, numeric_arg):

Purpose:

If the given date of a new year is in the middle of the week of the previous year then the count of the last week of the previous year is returned.

Example:
Select MID ( YEARWEEK ( ’ 2001-01-06 ’ ),5 ) returns ’ 53 ’

MINUTE(time_arg):

Purpose:

Returns the number of minutes present in the given time value. The time is given in the form 'hr : min : sec’

Example:

Select MINUTE (’ 220:22:34 ’) returns '22’

Note:

• Returns null if the value of minutes or seconds exceed '59’
• If the time is given in decimal form then '0’ is returned.

MONTH(date_arg):

Purpose:

This function returns the month of the given date.

Example:

Select MONTH ( ’ 2008-08-03 ’ ) returns '8’

Note:

• Returns '0’ if the given date contains '00’ months.
• The range of the year should be between 0 to 9999.

NOW():

Purpose:

The present date-time values of the application’s time zone is returned. The result is returned in the form of ’ yr : mth : dt hr : min : sec ’. Its function is same as LOCALTIMESTAMP()

Example:

Select NOW () returns '2008-08-23 12:59:41’

Note:

• The result is returned in numeric form or string according to the function’s use in the query.

PERIOD_ADD(numeric_arg, numeric_arg):

Purpose:

The argument contains the date and the number of months to be added to it. The date is passed in the format of period i.e 'yrmth’.

Example:

Select PERIOD_ADD ( 198608,06 ) returns ’ 198702 ’

Note:

• The date that is passed or returned is not a date value, but is a period value.
• If the date is passed in the usual form i.e in the format of 'yr:mth:dt’ then the result value that is returned is wrong.

PERIOD_DIFF(numeric_arg, numeric_arg):

Purpose:

2 dates in the form of period values are passed in the arguments. The number of months present between the 2 values is returned.

Example:

Select PERIOD_DIFF ( 198608, 198606 ) returns ’-10’

Note:

• Both the arguments should be in date-period form.
• Returns the result in positive if the second period is chronologically first.
• Returns the result in negative if the second period is chronologically second.

QUARTER(date_arg):

Purpose:

For the given date, the corresponding quarter of year is returned, ie returns '1’ if the months are 1,2,3; returns 2 if the months are 4,5,6; returns 3 if the months are 7,8 9 and returns 4 if the months are 10,11,12.

Example:

Select QUARTER ( ’ 2008-08-23 ’ ) returns '3’

Note:

• Returns '0’ if the given month is '0’.
• Returns null if the given date’s year, month or day is out of range.
• Returns the corresponding month’s quarter even if the year or day or both are '0’.

SEC_TO_TIME(numeric_arg):

Purpose:

The argument passed is the count of seconds of a day. It’s value in hours, minutes and seconds are returned.

Example:
Select SEC_TO_TIME ( 86399 ) returns ’ 23:59:59 ’

Note:

• At '86400’ the value of 'hr:min:sec’ are resetted to '0’ and the cycle starts again.
• As soon as a non-numeric character is found in the string then the SEC_TO_TIME of the numbers before the character is returned.
• Depending on the given argument format the result is given in the form of number or string.

SLEEP(numeric_arg):

Purpose:

This function is currently not supported by Zoho Analytics.

STR_TO_DATE(numeric_arg, string_arg):

Purpose:

Returns the date-time value after its conversion from the given string. The conversion is done after taking note of the format of the given string . The format is given as second argument.

Example:

Select STR_TO_DATE ( ’ 06/31/2004 ’, ’ %m/%d/%Y ’ ) returns ’ 2004-07-01 ’

Note:

• Returns null if the date, month or year exceeds their respective range.
• Returns the date or time value depending on the respective presence in the string.

SUBDATE(date.time_arg, numeric_arg):

Purpose:

Returns the date-time value after subtracting the number of days passed in the second argument from the date-time value passed in the first argument.

Example:

Select SUBDATE ( ’ 2008-02-19 12:00:00 ’, 31 ) returns ’ 2008 -01-19 12:00:00 ’
Select SUBDATE ( ’ 2008-02-19 ’, INTERVAL 31 DAY ) // This query is currently not supported by Zoho Analytics

Note:

• The range of years for which the function works correctly is '200-9999’. Returns wrong value if year is below '200’ and returns null if it is above '9999’
• Returns null if the month is a number other than 1-12.
• Returns null if the day is a number other than 1-31.

SUBTIME(date.time_arg, time_arg):

Purpose:

The first argument is a date-time value and the second argument is a time value. The subtraction value of second argument from the first argument is returned.

Example:

Select SUBTIME ( ’ 1986-08-03 18:45:00 ’ , ’ 03:45:00 ’ ) returns ’ 1986-08-03 15:00:00 ’

Note:

• Returns null if the date value is passed in the second argument.
• The range of years for which the function works correctly is '200-9999’. Returns wrong value if year is below '200’ and returns null if it is above '9999’.
• Returns null if the month is a number other than 1-12.
• Returns null if the day is a number other than 1-31.

SYSDATE():

Purpose:

The present date-time value of the application’s time zone is returned in the form of ’ yr : mth : dt hr : min : sec ’.

Example:

Select SYSDATE () returns ’ 2008-11-05 05:41:16 ’

Note:

• The result is returned in numeric form or string form according to the function’s use in the query.

TIME(date.time_arg):

Purpose:

From the given date-time value argument, the time value is returned.

Example:

Select TIME ( ’ 2008-11-05 17:34:45 ’ ) returns ’ 17:34:45 ’

Note:

• If the date value alone is passed, then the function assumes the year term as the time and returns it.
• Returns null if the date value is wrong.

TIMEDIFF(time_arg, time_arg):

Purpose:

Returns the difference between the 2 time values passed in the 2 arguments.

Example:

Select TIMEDIFF('20:08:05’,'20:07:34’) returns '00:00:31’

Note:

• The first argument should be greater than the second argument or else the function will not work.
• If date value is passed in the any one argument null is returned.
• If date value is passed in both the arguments then the function will not work.

TIMESTAMP(date.time_arg, time_arg):

Purpose:

The first argument contains both the date and time values. The second argument should contain only the time values. Both the arguments are added and the resultant date-time value is returned.

Example:

Select TIMESTAMP ( ’ 2008-11-05 19:00:00 ’ , ’ 06:00:00 ’ ) returns ’ 2008-11-06 01:00:00 ’

Note:

• Returns the date time values of the first argument alone if only the first argument is passed.
• Returns null if the second argument contains date values.

TIMESTAMPADD():

Purpose:

This function is currently not supported by Zoho Analytics.

TIMESTAMPDIFF():

Purpose:

This function is currently not supported by Zoho Analytics.

TIME_FORMAT(time_arg, string_arg):

Purpose:

The first argument is the time and the second argument is the format string containing format specifiers. The formatted value is returned.

Example:

Select TIME_FORMAT ( ’ 19:30:41.32 ’ , ’ %k %l %i %s %f ’ ) returns ’ 19 7 30 41 320000 ’

Note:

• The format string must contain only the time format specifiers.
• Even if a single non-time specifier is passed then only null value is returned.

TIME_TO_SEC(time_arg):

Purpose:

For the time value passed the total number of seconds is returned after converting the minutes and hours to seconds.

Example:

Select TIME_TO_SEC ( ’ 01:00:00 ’ ) returns ’ 3600 ’

TO_DAYS(date_arg):

Purpose:

Converts the passed date argument to the total number of days passed since the date '0001-01-01’

Example:

Select TO_DAYS ( ’ 2008-11-07 ’ ) returns ’ 733718 ’

Note:

• The range of years for which the function works correctly is '200-9999’. Returns wrong value if year is below '200’ and returns null if it is above '9999’.
• Returns null if the month is a number other than 1-12.
• Returns null if the day is a number other than 1-31.

UNIX_TIMESTAMP(date.time_arg):

Purpose:

Returns the number of seconds completed since the date ’ 1970-01-01 00:00:00 ’

Example:

Select UNIX_TIMESTAMP ( ’ 1970-01-01 00:30:00 ’ ) returns ’ 1800 ’

Note:

• Returns '0’ if a date earlier than the date ’ 1970-01-01 00:00:00 ’ is passed.

UTC_DATE():

Purpose:

The present UTC date is returned.

Example:

Select UTC_TIMESTAMP () returns ’ 2008-11-06 05:40:58 ’

Note:

• Returns the current datetime value as a number or a string value depending on the format of function used.

UTC_TIMESTAMP():

Purpose:

The present UTC date-time value is returned.

Example:

Select UTC_TIMESTAMP () returns ’ 2008-11-06 05:40:58 ’

Note:

• Returns the current datetime value as a number or a string value depending on the format of function used.

WEEK(date_arg):

Purpose:

Returns the week of the year in which the given date is present.

Example:

Select WEEK(’ 2008-01-14 ’) returns '2’

Note:

• By default the week is assumed to be starting on Sunday.
• If we want the week to start from Monday then we should pass a MODE in the second argument.

WEEKDAY(date_arg):

Purpose:

Returns the day of the week the given date is. Returns '0’ if its a Monday, '1’ if its Tuesday,……'6’ if it is Sunday.

Example:

Select WEEKDAY ( ’ 2008-11-06 ’ ) returns '3’ since its a thursday.

Note:

• The given argument can be a date-time value or only a date value.

WEEKOFYEAR(date_arg):

Purpose:

Returns the week of the year in which the given date is present.

Example:

Select WEEKOFYEAR ( ’ 2008-01-14 ’ ) returns '2’

Note:

• Unlike WEEK(), in this function the second argument cannot be used.

YEAR(date_arg):

Purpose:

This function returns the year of the given date.

Example:

Select YEAR ( ’ 2008-11-22 ’ ) returns ’ 2008 ’

Note:

• The range of the year should be between 0 to 9999.
• Returns null if the given date, month or year is wrong.

YEARWEEK(date_arg):

Purpose:

Starting the count of the first week from the date '0000-01-01’ the week of the given date is returned.

Example:

Select YEARWEEK ( ’ 2000-01-02 ’ ) returns ’ 200001 ’

Note:

• Returns null if the month is a number other than 1-12.
• Returns null if the day is a number other than 1-31.
• Returns null if the year is a number above 9999.

MySQL AGGREGATE FUNCTIONS

The MySQL in-built Aggregate functions supported by Zoho Analytics.

AVG(numeric_arg):

Purpose:

Returns the average of the given term’s values.

Example:

Select AVG (col1) from "tab5” returns '3’ // Here 'tab5’ is the table name and 'col1’ is the column name.

Note:

• Returns null if the column does not contain any values.
• Returns '0.0’ if a non-numeric character occurs in the values.

BIT_AND(numeric_arg):

Purpose

The Bitwise AND calculation is performed on the given table’s values and the result is returned.

Example:

SELECT BIT_AND (numCol) from “ allTypeTest ” returns '33’ // The Bitwise_AND of the values of 'numcol’ rows from the Workspace 'allTypeTest’ is returned.

Note:

• Returns null if the column does not contain any values.
• Returns '0’ if a non-numeric character occurs in the values.

BIT_OR(numeric_arg):

Purpose:

The Bitwise OR calculation is performed on the given table’s values and the result is returned.

Example:

SELECT BIT_OR (numCol) from “ allTypeTest ” returns '127’ // The Bitwise_OR of the values of 'numcol’ rows from the Workspace 'allTypeTest’ is returned.

Note:

• Returns null if the column does not contain any values.
• Returns '0’ if a non-numeric character occurs in the values.

BIT_XOR(numeric_arg):

Purpose:

The Bitwise XOR calculation is performed on the given table’s values and the result is returned.

Example:

SELECT BIT_XOR ( numCol ) from “ allTypeTest ” returns '121’ // The Bitwise_XOR of the values of 'numcol’ rows from the Workspace 'allTypeTest’ is returned.

Select ATAN ('0.5A@5’) returns '0.46364760900081’ // As soon as a non numeric character is found in the number string the number’s value is returned.

Note:

• Returns null if the column does not contain any values.
• Returns '0’ if a non-numeric character occurs in the values.

COUNT(numeric_arg):

Purpose:

Counts the number of rows present in the given table.

Example:

Select COUNT (numCol) from “ allTypeTest ” returns '6’ // since 'numcol’ column from the Workspace 'allTypeTest’ contains 6 rows.

Note:

• Returns '0’ if the column does not contain any values.
• The values can be both numbers and string.

GROUP_CONCAT(numeric_arg):

Purpose:

Concatenates the string values present in the given table.

Example:

Select GROUP_CONCAT ( plainCol ) from “allTypeTest” returns 'Hello,Hlo,Hello,Hlo,Hello,Hlo’

Select GROUP_CONCAT ( DISTINCT plainCol ) from “allTypeTest” returns 'Hello,Hlo’ // Does not repeat the output string.

Note:

• Returns null if the column does not contain any values.

MAX(string_arg, numeric_arg1, numeric_arg2):

Purpose:

The values present in the given columns are compared with each other and the maximum value is returned. The maximum value of both numbers and string are returned.

Example:

Select MAX (col3 ) from “tab2” returns 'sat’

Note:

• Returns null if the column does not contain any values.

MIN(numeric_arg):

Purpose:

The values present in the given columns are compared with each other and the minimum value is returned. The minimum value of both numbers and string are returned.

Example:

Select MIN (col3 ) from “tab2” returns 'chk’

Note:

• Returns null if the column does not contain any values.

STD(number1, number2):

Purpose:

The standard deviation of the given table’s numeric values are calculated and returned.

Example:

Select STD ( col2 ) from “ tab6 ” returns '8.0554’

Note:

• Returns null if the column does not contain any values.
• Returns '0.0’ if the column contains only non-numeric character values.

SUM(numeric_arg):

Purpose

The sum of the given table’s values are calculated and returned.

Example:

Select SUM ( col2 ) from “ tab6 ” returns '32.0’

Note:

• Returns null if the column does not contain any values.
• Returns '0.0’ if the column contains only non-numeric character values.

VARIANCE(numeric_arg):

Purpose:

The variance of the given table’s values are calculated and returned.The logarithm of the number to the base 10 is returned.

Example:

Select VARIANCE ( col2 ) from “ tab6 ” returns '64.8889’

Note:

• Returns null if the argument is null, negative or any non-numeric character.
• Returns '0.0’ if the column contains only non-numeric character values.