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