[Post] detailed usage of Informix SQL Functions

Source: http://dev.csdn.net/article/60/60822.shtm

Detailed usage of Informix SQL Functions

I. Internal functions
1. Internal Aggregate functions
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 value and of the specified column or expression;
4) sum (distinct colname) returns the sum of unique values in the specified Column
5) AVG (colname/expression) returns the average value of a specified column or expression.
6) AVG (distinct colname) returns the average value of the unique value in the specified Column
7) min (colname/expression) returns the minimum value of a specified column or expression.
8) max (colname/expression) returns the maximum value of a specified column or expression.
2. Date and Time Functions
1) Day (Date/datetime expression) returns the number of the current month in the specified expression
2) month (Date/datetime expression) returns the month in the specified expression
3) Year (Date/datetime expression) returns the year in the specified expression
4) weekday (Date/datetime expression) returns the number of weeks in the specified expression.
5) Date (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 datetime 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
10) datetime (Date/datetime expression) first to last returns the datetime value represented by the expression
11) interval (Date/datetime expression) first to last returns the time interval value represented by the expression
12) Extend (Date/datetime expression, [first to last]) returns the adjusted date or date time.

Value

Example 1: use it with units to specify the date or time unit (year, month, day, hour, minute, seond, fraction ):
Let tmp_date = Today + 3 units day
Example 2: Let tmp_date = mdy (2002,) -- 2002-10-30
Example 3: Let tmp_date = Today + interval (7) day to day -- the current time plus seven days;
Note: This function is similar to 1;
Example 4: Extend conversion date or Date and Time Value
Let tmp_inthour = extend (datetime1, hour to hour)

3. Algebraic Functions
1) ABS (colname/expression): obtains the absolute value.
2) mod (colname/expression, divisor) returns the modulus (remainder) after division by divisor)
3) Pow (colname/expression, exponent) returns the exponent of a value.
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 round value of the specified column or expression
7) trunc (colname/expression, [factor]) returns the truncation value of the specified column or expression.
Description: The number of decimal places specified by the factor in the preceding two items. If not specified, the decimal point is 0. If the number is negative, the decimal point is rounded to the left;
Note: Round performs 4 homes and 5 inputs on the positioning; trunc directly truncates the positioning;
Let tmp_float = round (4.555, 2) -- 4.56
Let tmp_float = trunc (4.555, 2) -- 4.55

4. exponent and logarithm Functions
1) exp (colname/expression) returns the exponent value of the specified column or expression
2) logn (colname/expression) returns the natural logarithm of the specified column or expression.
3) log10 (colname/expression) returns the logarithm of the base digit 10 of the specified column or expression.

5. trigonometric Functions
1) Cos (radian expression) returns the cosine of the specified radian expression
2) sin (radian expression) sine
3) Tan (radian expression) tangent
4) arccosine of ACOs (radian expression)
5) asin (radian expression) returns the arc sine.
6) atan (radian expression) arc tangent
7) atan2 (x, y) returns the Polar Coordinate angle component of the coordinate (x, y ).

6. Statistical functions
1) range (colname) returns the difference between the maximum and minimum values of the specified column = 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) The user returns the current user name.
2) hex (colname/expression) returns the hexadecimal value of the specified column or expression
3) length (colname/expression) returns the length 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: the character that is returned together;

Ii. IDS internal functions
1. dbservername: return the database server name let tmp_char = dbservername
2. sitename: the database server name let tmp_char = sitename is returned.
Note: The two functions are the same;

3. dbinfo ('special _ keyword') returns the keyword value only.
Example 1: return the dbspace name of each table in the data
Select dbinfo ('dbspace', partnum), tabname from orders Ables
Where Tabid> 99 and tabtype = 'T' (OK)
Example 2: return the last serial value inserted in any table
Select dbinfo ('sqlca. sqlerrd1 ') from tables Ables where Tabid = 1
Example 3: return the number of rows processed by the last select, insert, update, delete, or execute procedure statement;
Select dbinfo ('sqlca. sqlerrd2 ') from tables Ables where Tabid = 1;