Informix Common functions

I. Intrinsic 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 value of the specified column or expression;
4) SUM (DISTINCT colname) returns the number of unique values in the specified column and
5) AVG (colname/expression) returns the numeric mean in 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 numeric maximum 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) Date (not date expression) returns the day 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 for the specified precision
9) MDY (month,day,year) returns a date value that identifies the specified year, month, and day
DateTime (date/datetime expression) first to last returns the date time value represented by the expression
One) INTERVAL (date/datetime expression) first to last returns the time interval value represented by an expression
EXTEND (Date/datetime Expression,[first to Last]) returns an adjusted date or datetime

Value

Example 1, shared with units, specifies 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 (10,30,2002)--2002-10-30
Example 3, let Tmp_date = Today + interval (7) daily to 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) divided by the divisor
3) POW (colname/expression,exponent) returns an exponent of the value of the Ghost
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: The above two factor specify the number of decimal places, if not specified, is 0; if negative, the integer to the left of the decimal point;
Note: The round is 4 5 in the specified position, and trunc is truncated directly on 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 of values for 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 Functions
1) cos (RADIAN expression) returns the cosine value of the specified Radian expression
2) sin (RADIAN EXPRESSION) sine
3) Tan (RADIAN EXPRESSION) tangent
4) ACOS (RADIAN EXPRESSION) Inverse 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 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) 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 the specified column or expression
5) colname/expression | | Colname/expression returns and together the characters;

Ii. IDs intrinsic functions
1. DbServerName returns the database server name let Tmp_char=dbservername
2. SITENAME returns the database server name let Tmp_char=sitename
Description: Both functions are the same;

3, DBINFO (' Special_keyword ') returns only the keyword value
Example 1: The Dbspace name of each table in the returned 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;

Date manipulation

One of the more difficult concepts in Informix's handling of date and time values concerns the use of the variables in Ari Thmetic or relational expressions. You can add or subtract a DATE and DATETIME variables from each of the other. You can add or subtract an INTERVAL to a DATE or DATETIME. Table 1 shows the results of different types of operations on DATE and DATETIME values.

Table 1. Operations on DATE and DATETIME Variables

First Operand	Operation	Second Operand	Result
DATE	-	Datetime	INTERVAL
Datetime	-	DATE	INTERVAL
DATE	+-	INTERVAL	Datetime
Datetime	-	Datetime	INTERVAL
Datetime	+-	INTERVAL	Datetime
INTERVAL	+	Datetime	Datetime
INTERVAL	+-	INTERVAL	INTERVAL
Datetime	-	Current	INTERVAL
Current	-	Datetime	INTERVAL
INTERVAL	+	Current	Datetime
Current	+-	INTERVAL	Datetime
Datetime	+-	UNITS	Datetime
INTERVAL	+-	UNITS	INTERVAL
INTERVAL	*/	Number	INTERVAL

Notice that it's always okay to subtract one DATE or DATETIME value from another, as shown here:

Current-"07/01/1950" = INTERVAL (my age) "12/25/2000" –current = INTERVAL (How long till Xmas?)

In such a case, the result was always an INTERVAL variable. It would make no sense to add a DATE or DATETIME values together. What is could such an addition represent?

UNITS Keyword

When working with INTERVAL values, sometimes it was necessary to specify the precision with which you are dealing. For example, suppose you have the following field defined:

Lead_time INTERVAL Day

To add a-could use a-SQL statement like this:

SELECT Lead_time + INTERVAL (Ten) day to Dayfrom orders

You could achieve the same results using the UNITS keyword:

SELECT Lead_time + UNITS Day from Orders

Like most of other programming languages, SQL often allows your achieve the same ends with different statements. Sometimes the choice is one of the personal style. Other times, one format fits in better with a structured style of code writing than another format does.

Functions

There is several built-in functions that affect date and time calculations. They can apply to either DATE or DATETIME values, with some exceptions.

TODAY

The TODAY function returns a Date data value representing the current date. For example, you could execute a SQL function like this:

UPDATE member SET change_date = Todaywher Member_number = 12345

Current

The current function was similar to the TODAY function, except it returns a DATETIME value. Without specific qualifiers, the default is year to fraction (3). You can change the precision by using the-same year to fraction qualifiers as-a for datetimes. Thus, this would is legal:

SELECT * from Memberwhere Elapsed_time < current

DATE

The date function takes as input a non-date value such as CHAR, DATETIME, or INTEGER and returns the corresponding DATE VA Lue. For example, the following SQL translates a CHARACTER value to a DATE:

SELECT * from Memberwhere enrollment_date > Date (' 01/01/99 ')

Day

This function returns a integer representing the day of the month. Here's an example:

SELECT * from Memberwhere Day (enrollment_date) > Day (current)

MONTH

This performs like the day function except it returns an integer between 1 and representing the month:

SELECT * from Memberwhere enrollment_date > MONTH (' 01/01/99 ')

WEEKDAY

This returns a integer representing the day of the week, with 0 being Sunday and 6 being Saturday:

SELECT * from Memberwhere WEEKDAY (enrollment_date) > WEEKDAY (current)

Year

This function was like the ones above, and it returns a Four-digit integer representing the year.

EXTEND

This function allows the different precisions in a DATETIME than you have specified in the Declaration of the Varia ble. It uses the same first to last syntax as the DATETIME variables. This function was used to adjust the precision of a DATETIME variable to match the precision of a INTERVAL so you are US ing in a calculation. If the INTERVAL value has fields, is not part of the of the DATETIME value, which is the using in a calculation, use the Exte ND function to adjust the precision of the DATETIME. EXTEND can either increase or decrease the precision of a DATETIME, depending upon the first and last values.

Suppose MyVariable is declared as a DATETIME year-to-day. If you want to add or subtract a INTERVAL defined as MINUTE, you first has to extend the DATETIME as follows:

SELECT EXTEND (myvariable, year to MINUTE) –interval (5) MINUTE to Minutefrom member

The resulting value would be a DATETIME year to MINUTE.

MDY

The MDY function converts Three-integer values into a DATE format. The first integer is the month and must evaluate to a integer in the range 1–12. The second integer is the day and must evaluate to a number in the range from 1 to however many days be in the particular Month (28–31). The third expression is the year and must be a four-digit integer. Thus, the following MDY functions would each is valid:

MDY (7,1,1950)

Returns a DATE of "07/01/50"

MDY (today), 1, Year (today))

Returns a DATE equal to the first day of the "Current month"

Informix have extensive capabilities for manipulating dates and times, which can make for long and complex SQL statements. Using the three time-related data types and the time-related functions and keywords, you can accomplish almost any type of Manipulation of time data. Unfortunately, getting there may be cryptic and painful. If you regularly does extensive date and time manipulation, you should understand all of the intricacies of these data struc Tures.

Have fun!

Download from http://www.cppblog.com/wmuu/archive/2006/10/30/14381.html

Informix Common functions