Caigen Access Scalar Functions and Aggregate Functions

Index:

  1. Mathematical Functions
  2. Trigonometric Functions
  3. String Functions
  4. Date/Time Functions
  5. Boolean Functions
  6. System Functions
  7. Conversion Functions
  8. Security Functions
  9. Sequence Functions
  10. Miscellaneous Functions
  11. Aggregate Functions

Mathematical Functions

    1. ABS(x): the absolute value
    2. BITAND(x, y): returns the result of performing a bitwise AND on x and y.
    3. BIT_COUNT(x): returns the number of bits of x.
    4. CEIL(x), CEILING(x): the smallest integer that is not less than x
    5. DEGREES(x): converts radians to degrees
    6. EXP(x): exponential, e(2.718...) raised to the power of x
    7. FLOOR(x): the largest integer not greater than argument x
    8. INT(x) : truncates x to nearest integer
    9. LOG(x), LN(x): the natural logarithm
    10. LOG(b,x): returns the logarithm of X for an arbitary base B
    11. LOG10(x): the base 10 logarithm
    12. LOG2(X): the base 2 logarithm
    13. LN(x): the natural logarithm
    14. MOD(y, x): the remainder of y/x, you can use y%x too.
    15. PI(): pi constant, 3.14159265358979323846.
    16. POW(x, y), POWER(x, y): x raised to the power of y
    17. RADIANS(x): converts degrees to radians
    18. RAND([seed]): a random value between 0.0 and 1.0
    19. ROUND(x [,y]): rounds x to nearest integer without y, or round x to y digits after the decimal point.
    20. SIGN(x): returns -1 if x is smaller than 0, 0 if x==0 and 1 if x is bigger than 0.
    21. SQRT(x): the square root
    22. TRUNC(x[,y]), TRUNCATE(x[,y]): truncates x to nearest integer without y, truncates x to y digits after the decimal point

Trigonometric Functions

    1. ACOS(x): the inverse cosine of an angle
    2. ASIN(x): the inverse sine of an angle
    3. ATAN(x),ATN(x): the inverse tangent of an angle
    4. ATAN2(x, y): the inverse tangent of x/y
    5. COS(x): the cosine of an angle
    6. COT(x): the cotangent of an angle
    7. SIN(x): the sine of an angle
    8. TAN(x): the tangent of an angle

String Functions

    1. ALLTRIM(string1): removes all leading and trailing blanks in string1
    2. ASC(string1), ASCII(string1): the ASCII code of the leftmost character of the argument
    3. AT(cSearchExpression, cExpressionSearched [, nOccurrence]): returns the beginning numeric position of the first occurrence of a character expression or memo field within another character expression or memo field, counting from the leftmost character. If the character expression isn't found, AT( ) returns 0.
    4. ATC(cSearchExpression, cExpressionSearched [, nOccurrence]): returns the beginning numeric position of the first occurrence of a character expression or memo field within another character expression or memo field, counting from the leftmost character. If the character expression isn't found, AT( ) returns 0. It is case insensitive.
    5. BIN(number1): returns a string representation of the binary value of number1, where number1 is a integer(TINYINT, SMALLINT, INT, or BIGINT) number. Returns NULL if N is NULL.
    6. BIT_LENGTH(string1): the length of the string str in bits
    7. CHAR_LENGTH(string1), CHARACTER_LENGTH(string1): the number of characters in string1
    8. CHAR(integer), CHR(integer): a character with the given ASCII code
    9. CHAR(integer1,...): interprets the arguments as integers and returns a string consisting of the characters given by the unicode values of those integers. NULL values are skipped.
    10. CHRTRAN(cSearchedExpression, cSearchExpression, cReplacementExpression): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. CHRTRAN( ) translates the character expression cSearchedExpression using the translation expressions cSearchExpression and cReplacementExpression and returns the resulting character string. If a character in cSearchExpression is found in cSearchedExpression, the character in cSearchedExpression is replaced by a character from cReplacementExpression that's in the same position in cReplacementExpression as the respective character in cSearchExpression. If cReplacementExpression has fewer characters than cSearchExpression, the additional characters in cSearchExpression are deleted from cSearchedExpression. If cReplacementExpression has more characters than cSearchExpression, the additional characters in cReplacementExpression are ignored.
    11. CONCAT(string1, string2): string concatenation, you can use string1+string2 too.
    12. CONCAT(string1, string2,...): returns the string that results from concatenating the arguments. NULL values are skipped.
    13. CONCAT_WS(separator,string1, string2,...): returns the string that results from concatenating the arguments. The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. If the separator is NULL, the result is NULL. The function skips any NULL values after the separator argument.
    14. CONV(number1,base): returns a string representation of the first argument in the radix specified by the second argument. The minimum base is 2 and the maximum base is 36.
    15. DIFFERENCE(string1, string2): the difference between the sound of string1 and string2
    16. HEX(number1): returns a string representation of the hexadecimal value of number1, where number1 is a integer(TINYINT, SMALLINT, INT, or BIGINT) number. Returns NULL if N is NULL.
    17. INITCAP(string1): converts first letter of each word (whitespace-separated) to upper case
    18. INSERT(string1, start1, length1, string2): a string where length1 number of characters beginning at start1 has been replaced by string2
    19. INSTR(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1
    20. INSTR(start1, string1, string2): (Compatible purpose) the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1.
    21. LCASE(string1): converts string1 to lower case
    22. LEFT(string1, count1): the leftmost count1 of characters of string1
    23. LENGTH(string1), LEN(string1): the number of characters in string1
    24. LOCATE(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string1 is found in string2, starting at start1
    25. LOWER(string1): converts string1 to lower case
    26. LPAD(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the left. If the string is already longer than length then it is truncated (on the right).
    27. LTRIM(string1): removes all leading blanks in string1
    28. MID(string1 FROM start1 [FOR length1]), MID(string1, start1 [,length1]): extracts the substring starting at start1 with length length1. MID is a synonym for SUBSTRING.
    29. OCT(number1): returns a string representation of the octal value of number1, where number1 is a integer(TINYINT, SMALLINT, INT, or BIGINT) number. Returns NULL if N is NULL.
    30. OCTET_LENGTH(string1): the number of octets (8-bit bytes) needed to represent the string1.
    31. PADC(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on both sides. If the string is already longer than length then it is truncated (on the right).
    32. PADL(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the left. If the string is already longer than length then it is truncated (on the right).
    33. PADR(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the right. If the string is already longer than length then it is truncated (on the right).
    34. POSITION( s1 IN s2), POSITION(substr,str): location of specified substring
    35. PROPER(STRING1) : returns from a character expression a string capitalized as appropriate for proper names.
    36. REPEAT(string1, count1): repeats string1 count1 times
    37. REPLICATE(string1, count1): same as REPEAT(string1,count1)
    38. REPLACE(string1, string2, string3): replaces all occurrences in string1 of substring string2 with substring string3.
    39. RIGHT(string1, count1): the rightmost count1 of characters of string1
    40. RPAD(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the right. If the string is already longer than length then it is truncated (on the right).
    41. RTRIM(string1): removes all trailing blanks in string1
    42. SOUNDEX(string1): a four character code representing the sound of string1
    43. SPACE(nSpaces): returns a character string composed of a specified number of spaces.
    44. STRCAT(string1, string2): string concatenation, you can use string1+string2 too,same as CONCAT.
    45. STRCAT(string1, string2,...): returns the string that results from concatenating the arguments, NULL values are skipped,same as CONCAT.
    46. STRCMP(expr1,expr2): returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.
    47. STRCONV(expr1 [, charsetName]): returns a string by decoding the specified array of bytes using the specified charset. Cp895(Czech MS - DOS 895), Cp620(Polish MS - DOS 620) and Mazovia are extra supported although JVM doesn't support those. The omitted charsetName is 'ISO8859_1'.
    48. STRTRAN(cSearched, cSearchFor [, cReplacement][, nStartOccurrence] [, nNumberOfOccurrences]): searches a character expression or memo field for occurrences of a second character expression or memo field, and then replaces each occurrence with a third character expression or memo field.
    49. STUFF(cExpression, nStartReplacement, nCharactersReplaced, cReplacement): returns a string created by replacing a specified number of characters in a character expression with another character expression. cExpression specifies the string expression in which the replacement occurs. nStartReplacement specifies the position in cExpression where the replacement begins. nCharactersReplaced specifies the number of characters to be replaced. If nCharactersReplaced is 0, the replacement string cReplacement is inserted into cExpression. cReplacement specifies the replacement string expression. If cReplacement is the empty string, the number of characters specified by nCharactersReplaced are removed from cExpression.
    50. SUBSTR(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
    51. SUBSTRING(string1 FROM start1 [FOR length1]), SUBSTRING(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
    52. TRANSLATE(string1, string2, string3): any character in string1 that matches a character in the string2 is replaced by the corresponding character in the string3.
    53. TRIM([[BOTH | LEADING | TRAILING] [removedstring1] FROM] string1): remove the removedstring1 (a space by default) from the start/end/both ends of the string1.
    54. UCASE(string1): converts string1 to upper case
    55. UPPER(string1): converts string1 to upper case
    56. CHARMIRR(string1 [,lDontMirrorSpaces]): mirrors string1 at character level. string1 is the string that should be mirrored. If lDontMirrorSpaces equal to true, spaces at the end of string1 will not be mirrored but kept at the end. lDontMirrorSpaces's default value is false, which means to mirror the whole string.
    57. REVERSE(string1[,lDontMirrorSpaces]): mirrors string1 at byte level.

Date/Time Functions

    1. ADDTIME(expr,expr2): adds expr2 to expr and returns the result. expr is a date or timestamp expression, and expr2 is a time expression.
    2. CDOW(date) Returns the day-of-the-week(Sunday,Monday, Tuesday, Wednesday, Thursday, Friday,Saturday) from a given date,
    3. CMONTH(date) the name of the month
    4. CURDATE(): the current date
    5. CURTIME(): the current time
    6. DATE(): the current date
    7. DATE(expr): extracts the date part of the date or timestamp expression expr.
    8. DATETIME(): the current timestamp
    9. DATESERIAL(year,month,day): returns a date value representing a specified year, month, and day.
    10. DATE_ADD(date,INTERVAL expr type), DATE_SUB(date,INTERVAL expr type), ADDDATE(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type). For instance, SELECT DATE_ADD(date1,INTERVAL hour(now())+1 HOUR), adddate(date1,interval 3 hour) FROM test;
      type Value Expected expr Format
      MICROSECOND[S] MICROSECONDS
      MILLISECOND[S] MILLISECONDS
      SECOND SECONDS
      MINUTE MINUTES
      HOUR HOURS
      DAY DAYS
      WEEK WEEKS
      MONTH MONTHS
      QUARTER QUARTERS
      YEAR YEARS
      DECADE DECADES
      CENTURY CENTURYS
      MILLENNIUM MILLENNIUMS
      SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
      MINUTE_MICROSECOND 'MINUTES.MICROSECONDS'
      MINUTE_SECOND 'MINUTES:SECONDS'
      HOUR_MICROSECOND 'HOURS.MICROSECONDS'
      HOUR_SECOND 'HOURS:MINUTES:SECONDS'
      HOUR_MINUTE 'HOURS:MINUTES'
      DAY_MICROSECOND 'DAYS.MICROSECONDS'
      DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
      DAY_MINUTE 'DAYS HOURS:MINUTES'
      DAY_HOUR 'DAYS HOURS'
      YEAR_MONTH 'YEARS-MONTHS'
    11. DATEDIFF(expr,expr2): returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
    12. DATEADD(INTERVAL, expr, date), DATEDIFF (INTERVAL, date1, date2), DATEPART (INTERVAL, date) For instance, SELECT DateAdd('m', 3, date1) FROM test;
      INTERVAL Value Expected expr Format
      yyyy Year
      q Quarter
      m Month
      y Day of the year
      d Day
      w Weekday
      ww Week
      h Hour
      n Minute
      s Second
    13. DAY(date1), DAYOFMONTH(date1): the day of the month (1-31)
    14. DAYNAME(date1): the name of the day
    15. DAYOFWEEK(date1): the day of the week (1 means Sunday)
    16. DAYOFYEAR(date1): the day of the year (1-366)
    17. EXTRACT(type FROM expr): extracts parts from the date.
      type Value Expected Result
      MICROSECOND[S] MILLISECOND*1000
      MILLISECOND[S] indicats the millisecond within the second.
      SECOND indicats the second within the minute
      MINUTE MINUTES
      HOUR HOURS
      DAY DAYS
      MONTH MONTHS
      QUARTER QUARTERS
      YEAR YEARS
      DECADE DECADES
      CENTURY CENTURYS
      MILLENNIUM MILLENNIUMS
      DOW indicates the day of the week, SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY(1~7).
      DOY indicates the day number within the year. The first day of the year has value 1.
      WEEK,WOM indicats the ordinal number of the day of the week within the current month.
      WOY indicats the ordinal number of the day of the week within the current year.
      EPOCH the current time as UTC milliseconds from the epoch(1970-01-01 00:00:00).
    18. DOW(date1) get the day of the week, SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY(1~7)
    19. FROM_DAYS(expr1): given a day number expr1, returns a DATE value.
    20. GOMONTH(expr1,numMonths) : give a date,return the date before or after a number months
    21. HOUR(time1): the hour (0-23)
    22. LAST_DAY(date1): takes a date or timestamp value and returns the corresponding date for the last day of the month.
    23. MINUTE(time1): the minute (0-59)
    24. MILLISECOND(time1): the milliseconds from the time or timestamp expression time1.
    25. MICROSECOND(time1): the microseconds from the time or timestamp expression time1.
    26. MONTH(time1): the month (1-12)
    27. MONTHNAME(date1): the name of the month
    28. NOW(): the current date and time as a timestamp
    29. QUARTER(date1): the quarter (1-4)
    30. SECOND(time1): the second (0-59)
    31. SEC(time1) (Compatible purpose): the second (0-59)
    32. SUBTIME(expr,expr2): subtracts expr2 from expr and returns the result. expr is a date or timestamp expression, and expr2 is a time expression.
    33. SYSDATE(): the current date and time as a timestamp. Asynonym for NOW().
    34. TIME(): returns the current system time in 24-hour, eight-character string (hh:mm:ss) format.
    35. TIME(expr): extracts the time part of the time or timestamp expression expr.
    36. TIMEDIFF(expr,expr2) returns the time between the start time expr and the end time expr2. Only the time parts of the values are used in the calculation.
    37. TIMESERIAL(hour,minute,second): returns a Time value representing a specified hour, minute, and second.
    38. TIMESTAMP(expr): returns the date or timestamp expression expr as a timestamp value.
    39. TIMESTAMPADD(interval, count, timestamp1): adds the integer expression count to the date or timestamp expression timestamp1. interval can be SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR, FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
    40. TIMESTAMPDIFF(interval, timestamp1, timpestamp2): returns the integer difference between the date or timestamp expressions timestamp1 and timpestamp2 (timpestamp2-timestamp1). interval can be SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR, FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
    41. TO_DAYS(date1): given a date date1, returns a day number.
    42. WEEK(date1), WEEKOFYEAR(date1): the week of this year (1-53)
    43. YEAR(date1): the year

Boolean Functions

    1. BETWEEN(expression1,expression2,expression3) : determines whether the value of an expression1 lies between the expression2 and expression3, return true or false.
    2. EMPTY(expression): determines whether an expression evaluates to empty or null. The expression you include can be a string, numeric, date, or logical expression. EMPTY() returns true, when a string is empty string, spaces, tabs, carriage returns, linefeeds, or any combination of these, numeric value equals to 0, and logical expression is false.
    3. ISBLANK(expression): determines whether an expression evaluates to empty or null. The expression you include can be a string, numeric, date, or logical expression. ISBLANK() returns true, when a string is empty string or spaces, numeric value equals to null, and logical expression is null.
    4. ISALPHA(expression): determines whether the leftmost character in a character expression is alphabetic.
    5. ISDATE(expression): determines whether an expression can be converted to a date value.
    6. ISDIGIT(expression): determines whether the leftmost character of the specified character expression is a digit (0 through 9).
    7. ISDIGITS(expression): determines whether a string contains only digits(0 through 9).
    8. ISNULL(expression): determines whether an expression evaluates to null. The expression you include can be a string, numeric, date, or logical expression. If expression is NULL, ISNULL() returns true, otherwise it returns false.
    9. ISNUMERIC(expression): determines whether an expression can be converted to a number value.

System Functions

    1. DATABASE(): the name of the database of this connection
    2. USER(): the user name of this connection
    3. DELETED([cTableAlias | nWorkArea]): returns a logical value that indicates whether the current record is marked for deletion.
    4. RECCOUNT([cTableAlias | nWorkArea]): returns the number of records, which includes all deleted records.
    5. RECNO([cTableAlias | nWorkArea]): returns the current record number in the current or specified table. nWorkArea specifies the work area number for a table open in another work area. cTableAlias specifies the table alias for a table open in another work area.
    6. ROWLOCKED([cTableAlias | nWorkArea]): indicates whether the current row has been locked by process or application.
    7. TABLELOCKED(cTableName): indicates whether a table has been locked by process or application. For instance, select tablelocked('test').
    8. CURRVAL(cTableName, cColumnName): returns the last generated IDENTITY(auto_increment) value for a particuar table. If that table hasn't IDENTITY(auto_increment) column, it will return silently null value. Only when there's more than one auto_increment column in a table, it will check cColumnName's validity.

Conversion Functions

    1. CAST(expression AS data_type): converts value1 to another data type data_type. data_type may be SQL_BIGINT(Types.BIGINT), SQL_BINARY(Types.BINARY), SQL_BIT(Types.BIT), SQL_CHAR(Types.CHAR), SQL_DATE(Types.DATE), SQL_DECIMAL(Types.DECIMAL), SQL_DOUBLE(Types.DOUBLE), SQL_FLOAT(Types.FLOAT), SQL_INTEGER(Types.INTEGER), SQL_LONGVARBINARY(Types.LONGVARBINARY), SQL_LONGVARCHAR(Types.LONGVARCHAR), SQL_REAL(Types.REAL), SQL_SMALLINT(Types.SMALLINT), SQL_TIME(Types.TIME), SQL_TIMESTAMP(Types.TIMESTAMP), SQL_TINYINT(Types.TINYINT), SQL_VARBINARY(Types.VARBINARY), SQL_VARCHAR(Types.VARCHAR), BIGINT(Types.BIGINT), BINARY(Types.BINARY), BIT(Types.BIT), CHAR(Types.CHAR), DATE(Types.DATE), DECIMAL(Types.DECIMAL), DOUBLE(Types.DOUBLE), FLOAT(Types.FLOAT), INTEGER(Types.INTEGER), INT(Types.INTEGER), LONGVARBINARY(Types.LONGVARBINARY), LONGVARCHAR(Types.LONGVARCHAR), NUMERIC (Types.NUMERIC), REAL(Types.REAL), SMALLINT(Types.SMALLINT), TIME(Types.TIME), TIMESTAMP(Types.TIMESTAMP), TINYINT(Types.TINYINT), VARBINARY(Types.VARBINARY), and VARCHAR(Types.VARCHAR). For instance, cast('456' AS SQL_INTEGER),cast('123.456' AS DECIMAL(12,5)), and cast('2004-12-23' as sql_date).
    2. CONVERT(value1, SQLtype1): converts value1 to another data type SQLtype1. SQLtype1 may be SQL_BIGINT(Types.BIGINT), SQL_BINARY(Types.BINARY), SQL_BIT(Types.BIT), SQL_CHAR(Types.CHAR), SQL_DATE(Types.DATE), SQL_DECIMAL(Types.DECIMAL), SQL_DOUBLE(Types.DOUBLE), SQL_FLOAT(Types.FLOAT), SQL_INTEGER(Types.INTEGER), INT(Types.INTEGER), SQL_LONGVARBINARY(Types.LONGVARBINARY), SQL_LONGVARCHAR(Types.LONGVARCHAR), SQL_REAL(Types.REAL), SQL_SMALLINT(Types.SMALLINT), SQL_TIME(Types.TIME), SQL_TIMESTAMP(Types.TIMESTAMP), SQL_TINYINT(Types.TINYINT), SQL_VARBINARY(Types.VARBINARY), and SQL_VARCHAR(Types.VARCHAR). value1 may be any complicated expression. For instance, CONVERT("123",SQL_INTEGER).
    3. CBOOL(expression): returns a Boolean value from an expression.
    4. CBYTE(expression): returns a Byte value from an expression.
    5. CCUR(expression): returns a Currency value with four decimal digits of precision to the right of the decimal from an expression.
    6. CDATE(expression,pattern): returns a Date value according a pattern from an expression. For instance, CDATE('21111947','ddMMyyyy').
    7. CDBL(expression): returns a Double value from an expression.
    8. CINT(expression): returns an Integer value from an expression.
    9. CLNG(expression): returns a Long value from an expression.
    10. CSNG(expression): returns a Float value from an expression.
    11. CSTR(expression): returns a String value from an expression.
    12. CTOD(cExpression): converts a string expression to a date expression.
    13. CTOT(cExpression): returns a timestamp value from a string expression.
    14. DTOC(date1 | timestamp1[, 1]): returns a string from a date or timestamp expression.
    15. DTOT(dDateExpression): returns a timestamp value from a date expression.
    16. DTOS(date1 | timestamp1): returns a string in a yyyymmdd format from a specified date or timestamp expression.
    17. DTOS(date1 | timestamp1,pattern): returns a string according to a pattern format from a specified date or timestamp expression.
    18. TTOC(tExpression [, 1 | 2]): converts a timestamp expression to a string value of a specified format.
    19. TTOD(tExpression): returns a date value from a timestamp expression.
    20. POSIXTOT(expression): returns a timestamp value from a POSIX timestamp value.
    21. TTOPOSIX(tExpression): converts a timestamp expression to a POSIX timestamp value.
    22. STR(nExpression [, nLength [, nDecimalPlaces]]): Returns the character equivalent of a specified numeric expression. nExpression specifies the numeric expression STR( ) evaluates. nLength specifies the length of the character string STR( ) returns. The length includes one character for the decimal point and one character for each digit to the right of the decimal point. nDecimalPlaces specifies the number of decimal places in the character string STR( ) returns. If you specify fewer decimal places than are in nExpression, the extra digits are truncated. STR( ) pads the character string it returns with leading spaces if you specify a length larger than the number of digits to the left of the decimal point. STR( ) returns a string of asterisks, indicating numeric overflow, if you specify a length less than the number of digits to the left of the decimal point.
    23. STRZERO(nExpression, nLength[, nDecimals]): convert a numeric expression to a string padded with leading zeros.
    24. VAL(string1): returns a numeric value from a string1 composed of numbers.
    25. COLLATE(string1[,collation]): For multilingual sort in ORDER BY clause. Now collation can be 'DUTCH', 'GERMAN', 'ICELAND', 'SPANISH', 'RUSSIAN', 'CZECH', 'GREEK', 'SLOVAK', 'POLISH', 'TURKISH', 'HUNGARY', CP850, CP852, CP866, CROATIAN, HEBREW, SWEDISH, and 'MAZOVIA'. Without collation parameter, COLLATE function will try to utilize charSet property in Connection properties.
    26. PasToJava(str): get a Java string from a Pascal-style string
    27. JavaToPas(str): get a Pascal-style string from a Java string
    28. PasToJava(str): get a null-terminated string from a Pascal-style string
    29. CToPas(str): get a Pascal-style string from a null-terminated string
    30. CToJava(str): get a Java string from a null-terminated string
    31. JavaToC(str): get a null-terminated from a Java string
    32. BToInt_LE(binary): get int value from bytes with little-endian.
    33. BToInt_BE(binary): get int value from bytes with big-endian.
    34. IntToB_LE(binary): get bytes with little-endian from int value.
    35. IntToB_BE(binary): get bytes with big-endian from int value.
    36. BToShort_LE(binary): get short value from bytes with little-endian.
    37. BToShort_BE(binary): get short value from bytes with big-endian.
    38. ShortToB_LE(binary): get bytes with little-endian from short value.
    39. ShortToB_BE(binary): get bytes with big-endian from short value.
    40. BToLong_LE(binary): get long value from bytes with little-endian.
    41. BToLong_BE(binary): get long value from bytes with big-endian.
    42. LongToB_LE(binary): get bytes with little-endian from long value.
    43. LongToB_BE(binary): get bytes with big-endian from long value.
    44. GetNumber(str[, defaultValue]): return a number value(int, long, double) according to str. If failed to parse, return defaultValue(null is omitted value).
    45. GetInt(str[, defaultValue]): return an int value according to str. If failed to parse, return defaultValue(null is omitted value).
    46. GetLong(str[, defaultValue]): return a long value according to str. If failed to parse, return defaultValue(null is omitted value).
    47. GetDouble(str[, defaultValue]): return a double value according to str. If failed to parse, return defaultValue(null is omitted value).
    48. FORMAT(expression , format): eturns a string containing an expression formatted according to instructions contained in a format expression. Now format supports only date/timestamp pattern.

Security Functions

    1. COMPRESS(content) : Return a compressed byte[]
    2. UNCOMPRESS(compressedBytes) : Return an uncompressed byte[],please don't use it for non-compressed data
    3. ENCRYPT(content,cKey,cCryptMethod): Returns a crypted byte[]. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now. ENCRYPT function is used for VARBINARY column. Data Encryption Standard (DES) algorithm, adopted by the U.S. government in 1977, is a block cipher that transforms 64-bit data blocks under a 56-bit secret key, by means of permutation and substitution. It is officially described in FIPS PUB 46. The DES algorithm is used for many applications within the government and in the private sector. Triple-DES is an improvement over DES. It uses three DES keys k1, k2 and k3. A message is encrypted with k1 first, then decrypted with k2 and encrypted again with k3 (DESencryptiondecryptionencryption). This increases security as the key length effectively increases from 56 to 112 or 168 (two or three keys may be used in TriDES). The DES key size is 128 or 192 bit and block size 64 bit.
    4. DECRYPT(content,cKey,cCryptMethod): Returns a decrypted byte[]. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now.
    5. ENCODE(content): Encodes a BASE64 encoding string.
    6. DECODE(content): Returns a byte[] from a BASE64 string.
    7. ENCODE(content,cKey,cCryptMethod): Crypts and encodes content. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH'. ENCRYPT function is used for VARCHAR column.
    8. DECODE(content,cKey,cCryptMethod): Decodes and decrypts content. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now.
    9. MD5(string1): Calculates a MD5(Message-Digest Algorithm 5) checksum for the string1.
    10. SHA1(string1): Calculates a SHA-1(Secure Hash Algorithm 1) hash for the string1.
    11. Crypt3(word[, salt]): Returns a hashed string of 13 printable ASCII characters, with the first two characters represent the salt. It can be used to accept typed passwords from the user, or attempting to crack Unix passwords with a dictionary.

Sequence Functions

    1. NEXTVAL(cSequenceName): advances sequence and returns new value.
    2. CURRVAL(cSequenceName): returns value most recently obtained with nextval.

Miscellaneous Functions

Function
Argument Type
Return Type
Description

DECODE( expression , search , result [, search , result]... [, default] )

 expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found). same as argument type The decode function has the functionality of an IF-THEN-ELSE statement.

GREATEST(expression1,expression2[,...])

MAX(expression1,expression2[,...])

 any numeric, string, date/time, or boolean type same as argument type maximum value of all expressions

LEAST(expression1,expression2[,...])

MIN(expression1,expression2[,...])

 any numeric, string, date/time, or boolean type same as argument type minimum value of all expressions

IF(lExpression, eExpression1, eExpression2)

IIF(lExpression, eExpression1, eExpression2)

 

lExpression specifies the logical expression that IF()/IIF( ) evaluates.

 Returns one of two values depending on the value of a logical expression.
 If lExpression evaluates to true , eExpression1 is returned. If lExpression evaluates to false, eExpression2 is returned.

NVL(expression, value)

IFNULL(expression, value)

 any numeric, string, date/time, or boolean type Returns one of two values depending on whether expression is null. If expression evaluates to null , value is returned. Otherwise, expression is returned.
INLIST(eExpression1, eExpression2 [, eExpression3 ...]) eExpression1 specifies the expression INLIST( ) searches for in the set of expressions. eExpression2 [, eExpression3 ...] specifies the set of expressions to search. You must include at least one expression (eExpression2), and can include up to 24 expressions (eExpression2, eExpression3, and so on). Determines whether an expression matches another expression in a set of expressions. All the expressions in the set of expressions must be of the same data type.
COALESCE(value [, ...]) any numeric, string, date/time, or boolean type the type of the first of its arguments that is not null returns the first of its arguments that is not null
ELT(numberExpression,value1Expression,[value2Expression,...]) numberExpression must be a integer type,value expression can be any type Returns value depending on the numberExpression,value1Expression,...valuexExpression Returns value1Expression if numberExpression = 1, value2Expression if numberExpression = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.
INTERVAL(expression,expr1,expr2,...,exprn) any numeric, string, date/time, or boolean type integer value returns 0 if expression< expr1, 1 if expression< expr2 and so on or -1 if expressionN is NULL. If expression>exprn, returns n.
TRANSFER (expression, search_1, result_1)
TRANSFER (expression, search_1, result_1, search_2, result_2)
TRANSFER (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
TRANSFER (expression, search_1, result_1, default)
TRANSFER (expression, search_1, result_1, search_2, result_2, default)
TRANSFER (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) 		any numeric, string, date/time, or boolean type,or null Returns value depending on the expression,search_x,result_x and default TRANSFER compares expression to the search_x expressions and, if matches, returns result_x. If not, returns default, or, if default is left out, return null .
TRANSFORM(expression [, formatcodes]) expression specifies the character, currency, date, or numeric expression to format.
formatcode specifies one format code that determine how the expression is formatted. The following table lists the available format codes
Format CodeDescription
@CCR is appended to positive currency or numeric values to indicate a credit.
@Dact as DTOS function.
@Eact as DTOS function.
@Tleading and trailing spaces are trimmed from character values.
@Xdb is appended to negative currency or numeric values to indicate a debit.
@Zif 0, currency or numeric values are converted to spaces.
@(encloses negative currency or numeric values in parentheses.
@^converts currency or numeric values to scientific notation.
@0converts numeric or currency values to their hexadecimal equivalents. The numeric or currency value must be positive and less than 4,294,967,296.
! converts a character to uppercase.
$adds the current currency symbol specified by SET CURRENCY to currency and numeric values. By default, the symbol is placed immediately before or after the value. However, the currency symbol and its placement (specified with SET CURRENCY), the separator character (specified with SET SEPARATOR) and the decimal character (specified with SET POINT) can all be changed.
Xspecifies the width of character values. For example, if cFormatCodes is XX? 2 characters are returned.
Yconverts logical true (.T.) and false (.F.) values to Y and N, respectively.
@!converts a string to uppercase.
return the formatted string returns a character string from an expression in a format determined by a format code
  1. LASTNVL(column): returns the current value for a particuar column. When a null value is encountered, it will try to return the nearest non-null value of prior rows in the specified table. That function is useful when data are stored by using suppression of repeated data.
  2. LASTEVL(column): returns the current value for a particuar column. When an empty value is encountered, it will try to return the nearest non-empty value of prior rows in the specified table. That function is useful when data are stored by using suppression of repeated data. It means empty value when it is a null value, or a string is empty string, spaces, tabs, carriage returns, linefeeds, or any combination of these, numeric value equals to 0, and logical expression is false.

Aggregate Functions

    1. FIRST(expression): the value of a specified field in the first record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by this functions will be arbitrary.
    2. LAST(expression): the value of a specified field in the last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by this functions will be arbitrary.
    3. AVG(expression): the average (arithmetic mean) of all input values.
    4. AVG(DISTINCT expression): the average (arithmetic mean) of the distinct values of expression.
    5. COUNT(*): the number of input values.
    6. COUNT(expression): the number of input values for which the value of expression is not null.
    7. COUNT(DISTINCT expression): the number of different non-NULL values.
    8. MAX(expression): the maximum value of expression across all input values.
    9. MIN(expression): the minimum value of expression across all input values.
    10. STD(expression): the sample standard deviation of the input values.
    11. STDDEV(expression): the sample standard deviation of the input values.
    12. SUM(expression): the sum of expression across all input values.
    13. SUM(DISTINCT expression): the sum of the distinct values of expression.
    14. GROUP_CONCAT([DISTINCT] expr_list [order_by_clause] [SEPARATOR str_val]): returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. SEPARATOR is followed by the string value that should be inserted between values of result. The default is a comma (','). You can eliminate the separator altogether by specifying SEPARATOR ''. The result will be truncated to the maximum length of 8192 sometimes.
    15. GROUPING(expression): returns a value of 1 when the expression is added by either the CUBE or ROLLUP operator, or 0 when the expression is not the result of CUBE or ROLLUP. It is used to distinguish the null values that are returned by CUBE and ROLLUP from standard null values. The NULL returned as the result of a CUBE or ROLLUP operation is a special use of NULL.

     

Copyright © 2008-2019 Caigen Software. | All Rights Reserved. |