Detailed usage of Informix SQL functions

Function
Detailed usage of Informix SQL functions

One, internal functions
1. Internal aggregate function
1) Count (*) returns the number of rows
2 count (DISTINCT colname) returns the number of unique values in the specified column
3) SUM (colname/expression) returns the numeric value of the specified column or expression;
4) SUM (DISTINCT colname) returns the and of the unique value in the specified column
5 AVG (colname/expression) Returns the value average of the specified column or expression
6 AVG (DISTINCT colname) returns the average of the unique values in the specified column
7 min (colname/expression) returns the minimum value of a value in a specified column or expression
8 Max (colname/expression) returns the maximum value of the value in the specified column or expression
2, date and time function
1 day (Date/datetime EXPRESSION) returns the date of the month in the specified expression
2) MONTH (Date/datetime EXPRESSION) returns the month in the specified expression
3) year (Date/datetime EXPRESSION) returns the years in the specified expression
4) Weekday (Date/datetime EXPRESSION) returns the day of the week in the specified expression
5 Dates (not date EXPRESSION) returns the date value represented by the specified expression
6 Today returns the date value of the current date
7) Current[first to last] returns the date-time value of the current date
8) Colname/expression UNITS PRECISION Returns the specified number of units of the specified precision
9) MDY (month,day,year) returns the date value that identifies the specified year, month, and day
DATETIME (Date/datetime EXPRESSION)--The date-time value that the expression represents
INTERVAL (Date/datetime EXPRESSION) The value of the time interval that the expression represents
EXTEND (Date/datetime Expression,[first to Last]) returns the adjusted date or date time

Value

Example 1, and units, specify a date or time unit (year,month,day,hour,minute,seond,fraction):
Let Tmp_date = today + 3 UNITS Day
Example 2, let tmp_date = MDY (10,30,2002)--2002-10-30
Example 3, let Tmp_date = Now + interval (7) Day--The current time plus 7 days;
Note: This function is similar to 1;
Example 4, Extend conversion date or date time value
Let Tmp_inthour = Extend (Datetime1,hour to hour)

3. Algebraic functions
1) ABS (colname/expression): Take absolute value
2 mod (colname/expression,divisor) returns the modulo (remainder) after dividing the divisor
3) POW (colname/expression,exponent) returns an exponent of a value Pluto
Example: Let Tmp_float = Pow (2,3)--8.00000000

4) root (Colname/expression,[index) returns the root value of the specified column or expression

5) SQRT (colname/expression) returns the square root value of the specified column or expression

6) ROUND (Colname/expression,[factor]) returns the rounding value of the specified column or expression
7) TRUNC (Colname/expression,[factor]) returns the truncated value of the specified column or expression
Note: In both factor specify decimal places, if not specified, then 0; if negative, the integer to the left of the decimal point;
Note: The round is in the designated position to carry on 4 homes 5; Trunc is directly truncated at the specified bit;
Let Tmp_float = Round (4.555,2)--4.56
Let tmp_float = trunc (4.555,2)--4.55

4, exponential and logarithmic functions
1 EXP (colname/expression) returns the exponential value of the specified column or expression
2) Logn (colname/expression) returns the natural pair value of the specified column or expression
3) LOG10 (colname/expression) returns the pair value of the base bit 10 of the specified column or expression

5. Trigonometric function
1) COS (Radian EXPRESSION) returns the cosine of the specified radian expression
2 sin (radian EXPRESSION) sine
3) TAN (radian EXPRESSION) tangent
4) ACOS (Radian EXPRESSION) anti-cosine
5) ASIN (Radian EXPRESSION) anyway chord
6) Atan (Radian EXPRESSION) anyway cut
7) ATAN2 (x,y) return coordinates (X,Y) of the polar coordinate angle component

6. Statistical function
1) RANGE (colname) returns the difference between the maximum value of the specified column and the minimum value = Max (colname)-min

(colname)
2) Variance (colname) returns the sample variance of the specified column;
3) STDEV (colname) returns the standard deviation of the specified column;

7, other functions
1) User returns the current username
2) HEX (colname/expression) returns the hexadecimal value of the specified column or expression
3 Length (colname/expression) returns the lengths of the specified character column or expression
4) TRIM (colname/expression) deletes characters before and after a specified column or expression
5) colname/expression | | Colname/expression returned and together with the characters;

Ii. internal functions of IDs
1, DbServerName return to the database server name let Tmp_char=dbservername
2, SITENAME return to the database server name let Tmp_char=sitename
Description: Both functions are the same;

3, DBINFO (' Special_keyword ') returns only the key word value
Example 1: Returns the Dbspace name of each table in the data
Select Dbinfo (' Dbspace ', partnum), tabname from Systables
where tabid>99 and Tabtype= ' T ' (OK)
Example 2: Returns the last serial value inserted in any table
Select Dbinfo (' Sqlca.sqlerrd1 ') from systables where tabid = 1
Example 3: Returns the number of rows processed by the last select,insert,update,delete or execute procedure statement;
Select Dbinfo (' Sqlca.sqlerrd2 ') from Systables where tabid=1;