List of SQL Decision Tree Functions
CNXNAME (string_exp, nameformat_exp, nameoutput_exp)
The CNXNAME function is used to parse string_exp data fields so that they may be reconstituted into a more desired format.
The string_exp parameter is the field that contains the data.
The nameformat_exp parameter is a string value that is derived from the format of your data. Each part of the name is assigned a character (Last = 'L', First = 'F', Middle = 'M', Suffix (Jr., Sr., III, etc.) = 'S'). If there is a comma separating portions of the name, that is denoted with a 'C'. It is assumed that there will always be a space between portions of the name.
The nameoutput_exp parameter is a string value created from the same characters as the nameformat, in addition to a space (' ') character.
Example:
If the data field is in the format "Last<comma><space>First<space>Middle", the nameformat parameter would be 'LCFM'. If you wanted the output to be in the format "First<space>Last", the nameoutput parameter would be 'F L'. Using these values, the final function would resemble the following:
SELECT CNXName(NAME, 'LCFM', 'F L') from MyTable
CNXPREFERENCE( likeclause_count, like_clause_1, like_clause_2, ... , criteria1_exp, value1_exp, criteria2_exp, value2_exp, ... )
The CNXPreference function accepts an ordered preference list of like clauses and a list of value/output pairs. The function uses the LIKE operator to compare each of the like_clause items to each of the criteria values in the criteria/value pair. The output value is the first value from the criteria/value pairs where the criteria matches any of the like clauses. All of the pairs are first compared against like clause #1, then the pairs are then compared against like clause #2, etc, until a match is found. If no match is found, NULL is returned.
The following example will first attempt to return a local address. If a local address cannot be found, it returns a permanent address.
Example:
File | Example |
Contacts | Company |
| Contact |
| Title |
| Phone_Type_1 |
| Phone_Number_1 |
| Phone_Type_2 |
| Phone_Number_2 |
| Phone_Type_3 |
| Phone_Number_3 |
| Address_Type_1 |
| Address_1 |
| Address_Type_2 |
| Address_2 |
| Address_Type_3 |
| Address_3 |
Return the contacts address and phone information. The address should be the contacts mailing address (type 'M'); if a mailing address does not exist, use the permanent address (type 'P'). The phone number should be their office phone number (type 'O'); and if an office phone number does not exist, use the assistant's phone number (type 'A').
The following SQL Statement, using the CNXPreference, returns five columns (Company, Contact, Title, Address, Phone).
SELECT
contacts.Company,
contacts.Contact,
contacts.Title,
cnxpreference(2, "%M%", "%P%",
contacts.Address_Type_1,contacts.Address_1,
contacts.Address_Type_2,contacts.Address_2,
contacts.Address_Type_3,contacts.Address_3) as Address,
cnxpreference(2, "%O%", "%A%",
contacts.Phone_Type_1,Phone_Number_1,
contacts.Phone_Type_2,Phone_Number_2,
contacts.Phone_Type_3,Phone_Number_3) as Phone
FROM
Contacts
COALESCE(exp1, exp2, ...)
Returns the first non-null expression from the list of supplied expressions. If all expressions are NULL, returns NULL. The data types of the supplied expressions must be compatible.
Example: SELECT COALESCE(NULL, NULL, 123, 456) returns 123.
IF(criteria_exp,then_exp,else_exp)
Returns then_exp if criteria_exp evaluates to a non-zero value.
If criteria_exp evalulates to zero, then the function returns else_exp.
Examples:
Select If(1, 'YES', 'NO') returns YES.
Select If(0, 'YES', 'NO') returns NO.
IFEMPTY (exp1, exp2)
If exp1 is empty (white space) or null, exp2 is returned, otherwise exp1 is returned. The possible data type or type of exp2 must be compatible with the data type of exp1.
Examples:
SELECT IFEMPTY( NULL , 456) returns 456.
SELECT IFEMPTY(123, 456) returns 123.
SELECT IFEMPTY('', 'ABC') returns ABC.
IFNULL(exp1, exp2)
If exp1 is null, exp2 is returned, otherwise exp1 is returned. The possible data type or types of exp1 must be compatible with the data type of exp2.
Example: SELECT IFNULL(123, 456) returns 123.
NULLIF(exp1, exp2)
If exp1 equals exp2, then NULL is returned, otherwise exp1 is returned.
Examples:
SELECT NULLIF(123, 123) returns NULL.
SELECT NULLIF(123, 234) returns 123.
SWITCH(criteria_exp, compare1_exp, return1_exp, compare2_exp, return2_exp,...,[default_return_exp])
Returns return#_exp if criteria_exp evaluates to compare#_exp, or possibly default_return_exp if there are no matches. If the criteria_exp does not match any of the compare expressions, then the default_return_exp will be return if supplied. If the criteria_exp does not match any of the compare expressions and the default_return_exp was not specified, NULL is returned.
Note:
You can also use the DECODE function to get the same result.
Examples:
Select Switch(2, 1, 'YES', 2, 'NO', 3, 'POSSIBLY') returns NO.
Select Switch(3, 1, 'YES', 2, 'NO', 3, 'POSSIBLY') returns POSSIBLY.
Select Switch(20, 1, 'YES', 2, 'NO', 3, 'POSSIBLY', 'UNKNOWN' ) returns UNKNOWN.