Informix Guide to SQL: Tutorial Using Functions in Select Statements

You can use any basic type of expression (column, constant, function, aggregate function, and procedure), or combination thereof, in the select list.

A function expression uses a function that is evaluated for each row in the query. All function expressions require arguments. This set of expressions contains the time function and the length function when they are used with a column name as an argument.

All Informix database servers support the following aggregate functions:

• AVG
• COUNT
• MAX
• MIN
• RANGE
• STDEV
• SUM
• VARIANCE

An aggregate function returns one value for a set of queried rows. The aggregate functions take on values that depend on the set of rows that the WHERE clause of the SELECT statement returns. In the absence of a WHERE clause, the aggregate functions take on values that depend on all the rows that the FROM clause forms.

You cannot use aggregate functions for expressions that contain the following data types:

• TEXT
• BYTE
• CLOB
• BLOB
• opaque data types (unless user creates aggregate functions)
• collection data types
• row types

Aggregates are often used to summarize information about groups of rows in a table. This use is discussed in Chapter 5. When you apply an aggregate function to an entire table, the result contains a single row that summarizes all the selected rows.

Query 4-1 counts and displays the total number of rows in the stock table.

SELECT  COUNT(*)

	FROM  stock


 






 Query Result 4-1 

Query 4-2 includes a WHERE clause to count specific rows in the stock table; in this case, only those rows that have a manu_code of SHM.

SELECT  COUNT (*)

	FROM  stock

	WHERE  manu_code  =  'SHM'


 






 Query Result 4-2 

By including the keyword DISTINCT (or its synonym UNIQUE) and a column name in Query 4-3, you can tally the number of different manufacturer codes in the stock table.

SELECT  COUNT (DISTINCT  manu_code)

	FROM  stock


 






 Query Result 4-3 

Query 4-4 computes the average unit_price of all rows in the stock table.

SELECT  AVG (unit_price)

	FROM  stock


 






 Query Result 4-4 







 

Query 4-5 computes the average unit_price of just those rows in the stock table that have a manu_code of SHM.

SELECT  AVG (unit_price)

	FROM  stock

	WHERE  manu_code  =  'SHM'


 






 Query Result 4-5 

You can combine aggregate functions in the same SELECT statement. For example, you can include both the MAX and the MIN functions in the select list, as Query 4-6 shows.

SELECT  MAX (ship_charge),  MIN (ship_charge)

	FROM  orders

Query 4-6 finds and displays both the highest and lowest ship_charge in the orders table, as Query Result 4-6 shows.

Query Result 4-6

Query 4-7 calculates the total ship_weight of orders that were shipped on July 13, 1998.

SELECT  SUM (ship_weight)

	FROM  orders

	WHERE  ship_date  =  '07/13/1998' 


 






 Query Result 4-7 







 

The RANGE function computes the difference between the maximum and the minimum values for the selected rows.

You can apply the RANGE function only to numeric columns. Query 4-8 finds the range of prices for items in the stock table.

Query Result 4-8

As with other aggregates, the RANGE function applies to the rows of a group when the query includes a GROUP BY clause, which Query 4-9 shows.

SELECT  RANGE(unit_price) FROM stock
	GROUP BY manu_code


 






 Query Result 4-9 







 

The STDEV function computes the standard deviation for the selected rows. It is the square root of the VARIANCE function.

You can apply the STDEV function only to numeric columns. The following query finds the standard deviation on a population:

As with the other aggregates, the STDEV function applies to the rows of a group when the query includes a GROUP BY clause, as the following example shows:

Nulls are ignored unless every value in the specified column is null. If every column value is null, the STDEV function returns a null for that column. For more information about the STDEV function, see the Expression segment in the Informix Guide to SQL: Syntax.

The VARIANCE function returns the variance for a sample of values as an unbiased estimate of the variance for all rows selected. It computes the following value:

In this example, Xi is each value in the column and N is the total number of values in the column. You can apply the VARIANCE function only to numeric columns. The following query finds the variance on a population:

As with the other aggregates, the VARIANCE function applies to the rows of a group when the query includes a GROUP BY clause, which the following example shows:

Nulls are ignored unless every value in the specified column is null. If every column value is null, the VARIANCE function returns a null for that column. For more information about the VARIANCE function, see the Expression segment in the Informix Guide to SQL: Syntax.

Query 4-10 shows how you can apply functions to expressions, and you can supply display labels for their results.

SELECT MAX (res_dtime  -  call_dtime)  maximum,

	MIN (res_dtime  -  call_dtime)  minimum,

	AVG (res_dtime  -  call_dtime)  average

	FROM  cust_calls

Query 4-10 finds and displays the maximum, minimum, and average amount of time (in days, hours, and minutes) between the reception and resolution of a customer call and labels the derived values appropriately. Query Result 4-10 shows these amounts of time.

Query Result 4-10

You can use the time functions DAY, MDY, MONTH, WEEKDAY, and YEAR in either the SELECT clause or the WHERE clause of a query. These functions return a value that corresponds to the expressions or arguments that you use to call the function. You can also use the CURRENT function to return a value with the current date and time, or use the EXTEND function to adjust the precision of a DATE or DATETIME value.

Query 4-11 returns the day of the month for the call_dtime and res_dtime columns in two expression columns.

SELECT  customer_num,  DAY (call_dtime),  DAY (res_dtime)

	FROM  cust_calls 



 






 Query Result 4-11 







 

Query 4-12 uses the DAY and CURRENT functions to compare column values to the current day of the month. It selects only those rows where the value is earlier than the current day. In this example, the CURRENT day is 15.

SELECT  customer_num,  DAY (call_dtime),  DAY (res_dtime)

	FROM  cust_calls

	WHERE  DAY (call_dtime)  <  DAY (CURRENT)


 




customer_num    (expression)  (expression)  



                  106                        12                      12

                  110                          7                        7

                  119                          1                        2

                  121                        10                      10



 Query Result 4-12 







 

Query 4-13 uses the CURRENT function to select all calls except those that came in today.

SELECT  customer_num,  call_code,  call_descr

	FROM  cust_calls

	WHERE  call_dtime  <  CURRENT  YEAR  TO  DAY 


 






 Query Result 4-13 







 

Query 4-14 uses the MONTH function to extract and show what month the customer call was received and resolved, and it uses display labels for the resulting columns. However, it does not make a distinction between years.

SELECT customer_num,

	MONTH (call_dtime)  call_month,  

	MONTH (res_dtime)  res_month

	FROM  cust_calls


 






 Query Result 4-14 







 

Query 4-15 uses the MONTH function plus DAY and CURRENT to show what month the customer call was received and resolved if DAY is earlier than the current day.

SELECT customer_num,

	MONTH (call_dtime)  called,  

	MONTH (res_dtime)  resolved

	FROM  cust_calls

	WHERE  DAY (res_dtime)  <  DAY (CURRENT) 


 






 Query Result 4-15 







 

Query 4-16 uses the WEEKDAY function to indicate which day of the week calls are received and resolved (0 represents Sunday, 1 is Monday, and so on), and the expression columns are labeled.

SELECT customer_num,  

	WEEKDAY (call_dtime)  called,  

	WEEKDAY (res_dtime)  resolved  

	FROM  cust_calls

	ORDER  BY  resolved 


 






 Query Result 4-16 

Query 4-17 uses the COUNT and WEEKDAY functions to count how many calls were received on a weekend. This kind of statement can give you an idea of customer-call patterns or indicate whether overtime pay might be required.

SELECT  COUNT(*)  

	FROM  cust_calls

	WHERE  WEEKDAY (call_dtime)  IN  (0,6) 

 






 Query Result 4-17 

Query 4-18 retrieves rows where the call_dtime is earlier than the beginning of the current year.

SELECT customer_num,  call_code,  

	YEAR (call_dtime)  call_year,

	YEAR (res_dtime)  res_year

	FROM  cust_calls

	WHERE  YEAR (call_dtime)  <  YEAR (TODAY) 

 






 Query Result 4-18 

In Query 4-19, the EXTEND function displays only the specified subfields to restrict the two DATETIME values.

SELECT customer_num,  

	EXTEND (call_dtime,  month  to  minute)  call_time,

	EXTEND (res_dtime,  month  to  minute)  res_time

	FROM  cust_calls

	ORDER  BY  res_time

Query Result 4-19 returns the month-to-minute range for the columns labeled call_time and res_time and gives an indication of the work load.

Query Result 4-19

The following conversion functions convert between date and character values:

• DATE
• TO_CHAR
• TO_DATE

You can use a date-conversion function anywhere you use an expression.

The DATE function converts a character string to a DATE value. In Query 4-20, the DATE function converts a character string to a DATE value to allow for comparisons with DATETIME values. The query retrieves DATETIME values only when call_dtime is later than the specified DATE.

SELECT  customer_num,  call_dtime,  res_dtime

	FROM  cust_calls

	WHERE  call_dtime  >  DATE ('12/31/97')

 






 Query Result 4-20 







 

Query 4-21 converts DATETIME values to DATE format and displays the values, with labels, only when call_dtime is greater than or equal to the specified date.

SELECT  customer_num,

	DATE (call_dtime)  called,  

	DATE (res_dtime)  resolved

	FROM  cust_calls

	WHERE  call_dtime  >=  DATE ('1/1/98')

 






 Query Result 4-21 

The TO_CHAR function converts DATETIME or DATE values to character string values. The TO_CHAR function evaluates a DATETIME value according to the date-formatting directive that you specify and returns an NVARCHAR value. For a complete list of the supported date-formatting directives, see the description of the GL_DATETIME environment variable in the Informix Guide to GLS Functionality.

You can also use the TO_CHAR function to convert a DATETIME or DATE value to an LVARCHAR value.

Query 4-22 uses the TO_CHAR function to convert a DATETIME value to a more readable character string.

SELECT customer_num, 
	TO_CHAR(call_dtime, "%A %B %d %Y") call_date 
	FROM cust_calls
	WHERE call_code = "B" 

 






 Query Result 4-22 







 

Query 4-23 uses the TO_CHAR function to convert DATE values to more readable character strings.

SELECT order_num, 
	TO_CHAR(ship_date,"%A %B %d %Y") date_shipped
	FROM orders
	WHERE paid_date IS NULL

 






 Query Result 4-23 







 

The TO_DATE function accepts an argument of a character data type and converts this value to a DATETIME value. The TO_DATE function evaluates a character string according to the date-formatting directive that you specify and returns a DATETIME value. For a complete list of the supported date-formatting directives, see the description of the GL_DATETIME environment variable in the Informix Guide to GLS Functionality.

You can also use the TO_DATE function to convert an LVARCHAR value to a DATETIME value.

Query 4-24 uses the TO_DATE function to convert character string values to DATETIME values whose format you specify.

SELECT customer_num, call_descr
	FROM cust_calls
	WHERE call_dtime = TO_DATE("1998-07-07 10:24",
	"%Y-%m-%d %H:%M").


 






 Query Result 4-24 







 

You can use the DATE or TO_DATE function to convert a character string to a DATE value. One advantage of the TO_DATE function is that it allows you to specify a format for the value returned. (You can use the TO_DATE function, which always returns a DATETIME value, to convert a character string to a DATE value because the database server implicitly handles conversions between DATE and DATETIME values.)

Query 4-25 uses the TO_DATE function to convert character string values to DATE values whose format you specify.

SELECT order_num, paid_date
	FROM orders
	WHERE order_date = TO_DATE("6/7/98", "%m/%d/%iY") 

 






 Query Result 4-25 







 

The CARDINALITY() function counts the number of elements that a collection contains. You can use the CARDINALITY() function with simple or nested collections. Any duplicates in a collection are counted as individual elements. Query 4-26 shows a query that returns, for every row in the manager table, department values and the number of elements in each direct_reports collection.

SELECT department, CARDINALITY(direct_reports)
	FROM manager

 






 Query Result 4-26 







 

You can also evaluate the number of elements in a collection from within a predicate expression, as Query 4-27 shows.

SELECT department, CARDINALITY(direct_reports)
	FROM manager
	WHERE CARDINALITY(direct_reports) < 6 
	GROUP BY department

 






 Query Result 4-27 

The database server provides four SQL functions that you can call from within an SQL statement to import and export smart large objects. Figure 4-1 shows the smart-large-object functions.

For detailed information and the syntax of smart-large-object functions, see the Expression segment in the Informix Guide to SQL: Syntax.

You can use any of the functions that Figure 4-1 shows in SELECT, UPDATE, and INSERT statements. For examples of how to use the preceding functions in INSERT and UPDATE statements, see Chapter 6.

Suppose you create the inmate and fbi_list tables, as Figure 4-2 shows.

CREATE TABLE inmate 
(
	id_num 		INT, 
	picture		BLOB,
	felony 		CLOB
)


CREATE TABLE fbi_list 
( 
	id 		INTEGER,
	mugshot		 BLOB
) PUT mugshot IN (sbspace1)

The following SELECT statement uses the LOTOFILE() function to copy data from the felony column into the felon_322.txt file that is located on the client computer:

SELECT id_num, LOTOFILE(felony, 'felon_322.txt', 'client')
	FROM inmate
	WHERE id = 322

The first argument for LOTOFILE() specifies the name of the column from which data is to be exported. The second argument specifies the name of the file into which data is to be copied. The third argument specifies whether the target file is located on the client computer ('client') or server computer ('server').

The following rules apply for specifying the path of a filename in a function argument, depending on whether the file resides on the client or server computer:

• If the source file resides on the server computer, you must specify the full pathname to the file (not the pathname relative to the current working directory).
• If the source file resides on the client computer, you can specify either the full or relative pathname to the file.

String-manipulation functions accept arguments of type CHAR, NCHAR, VARCHAR, NVARCHAR, or LVARCHAR. You can use a string-manipulation function anywhere you use an expression.

The following functions convert between upper and lowercase letters in a character string:

• LOWER
• UPPER
• INITCAP

The following functions manipulate character strings in various ways:

• REPLACE
• SUBSTR
• SUBSTRING
• LPAD
• RPAD

You cannot overload any of the string-manipulation functions to handle extended data types.

Use the LOWER function to replace every uppercase letter in a character string with a lowercase letter. The LOWER function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

Query 4-28 uses the LOWER function to convert any uppercase letters in a character string to lowercase letters.

SELECT manu_code, LOWER(manu_code)
	FROM items
	WHERE order_num = 1018

 






 Query Result 4-28 







 

Use the UPPER function to replace every lowercase letter in a character string with an uppercase letter. The UPPER function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

Query 4-29 uses the UPPER function to convert any uppercase letters in a character string to lowercase letters.

SELECT call_code, UPPER(code_descr)
	FROM call_type

 






 Query Result 4-29 







 

Use the INITCAP function to replace the first letter of every word in a character string with an uppercase letter. The INITCAP function assumes a new word whenever the function encounters a letter that is preceded by any character other than a letter. The INITCAP function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

Query 4-30 uses the INITCAP function to convert the first letter of every word in a character string to an uppercase letter.

SELECT INITCAP(description) 
	FROM stock
	WHERE manu_code = "ANZ" 

 






 Query Result 4-30 

Use the REPLACE function to replace a certain set of characters in a character string with other characters.

In Query 4-31, the REPLACE function replaces the unit column value each with item for every row that the query returns. The first argument of the REPLACE function is the expression to be evaluated. The second argument specifies the characters that you want to replace. The third argument specifies a new character string to replace the characters removed.

SELECT stock_num, REPLACE(unit,"each", "item") cost_per, 
unit_price
	FROM stock
	WHERE manu_code = "HRO"

 






 Query Result 4-31 

You can use the SUBSTRING and SUBSTR functions to return a portion of a character string. You specify the start position and length (optional) to determine which portion of the character string the function returns.

You can use the SUBSTRING function to return some portion of a character string. You specify the start position and length (optional) to determine which portion of the character string the function returns. You can specify a positive or negative number for the start position. A start position of 1 specifies that the SUBSTRING function begins from the first position in the string. When the start position is zero (0) or a negative number, the SUBSTRING function counts backward from the beginning of the string.

Query 4-32 shows an example of the SUBSTRING function, which returns the first four characters for any sname column values that the query returns. In this example, the SUBSTRING function starts at the beginning of the string and returns four characters counting forward from the start position.

SELECT sname, SUBSTRING(sname FROM 1 FOR 4)
	FROM state 
	WHERE code = "AZ"

 






 Query Result 4-32 

In Query 4-33, the SUBSTRING function specifies a start position of 6 but does not specify the length. The function returns a character string that extends from the sixth position to the end of the string.

SELECT sname, SUBSTRING(sname FROM 6)
	FROM state 
	WHERE code = "WV" 

 






 Query Result 4-33 

In Query 4-34, the SUBSTRING function returns only the first character for any sname column value that the query returns. For the SUBSTRING function, a start position of -2 counts backward three positions (0, -1, -2) from the start position of the string (for a start position of 0, the function counts backward one position from the beginning of the string).

SELECT sname, SUBSTRING(sname FROM -2 FOR 4)
	FROM state 
	WHERE code = "AZ" 

 






 Query Result 4-34 

The SUBSTR function serves the same purpose as the SUBSTRING function, but the syntax of the two functions differs.

To return a portion of a character string, specify the start position and length (optional) to determine which portion of the character string the SUBSTR function returns. The start position that you specify for the SUBSTR function can be a positive or a negative number. However, the SUBSTR function treats a negative number in the start position differently than does the SUBSTRING function. When the start position is a negative number, the SUBSTR function counts backward from the end of the character string, which depends on the length of the string, not the character length of a word or visible characters that the string contains. The SUBSTR function recognizes zero (0) or 1 in the start position as the first position in the string.

Query 4-35 shows an example of the SUBSTR function that includes a negative number for the start position. Given a start position of -15, the SUBSTR function counts backward 15 positions from the end of the string to find the start position and then returns the next five characters.

SELECT sname, SUBSTR(sname, -15, 5)
	FROM state
	WHERE code = "CA"


 






 Query Result 4-35 

To use a negative number for the start position, you need to know the length of the value that is evaluated. The sname column is defined as CHAR(15), so a SUBSTR function that accepts an argument of type sname can use a start position of 0, 1, or -15 for the function to return a character string that begins from the first position in the string.

Query 4-36 returns the same result as Query 4-35.

SELECT sname, SUBSTR(sname, 1, 5)
	FROM state
	WHERE code = "CA

Use the LPAD function to return a copy of a string that has been left padded with a sequence of characters that are repeated as many times as necessary or truncated, depending on the specified length of the padded portion of the string. Specify the source string, the length of the string to be returned, and the character string to serve as padding.

The data type of the source string and the character string that serves as padding can be any data type that converts to VARCHAR or NVARCHAR.

Query 4-37 shows an example of the LPAD function with a specified length of 21 bytes. Because the source string has a length of 15 bytes (sname is defined as CHAR(15)), the LPAD function pads the first six positions to the left of the source string.

SELECT sname, LPAD(sname, 21, "-")
	FROM state
	WHERE code = "CA" OR code = "AZ" 

 






 Query Result 4-37 

Use the RPAD function to return a copy of a string that has been right padded with a sequence of characters that are repeated as many times as necessary or truncated, depending on the specified length of the padded portion of the string. Specify the source string, the length of the string to be returned, and the character string to serve as padding.

The data type of the source string and the character string that serves as padding can be any data type that converts to VARCHAR or NVARCHAR.

Query 4-38 shows an example of the RPAD function with a specified length of 21 bytes. Because the source string has a length of 15 bytes (sname is defined as CHAR(15)), the RPAD function pads the first six positions to the right of the source string.

SELECT sname, RPAD(sname, 21, "-")
	FROM state
	WHERE code = "WV" OR code = "AZ" 

 






 Query Result 4-38 

You can also use the LENGTH, USER, CURRENT, and TODAY functions anywhere in an SQL expression that you would use a constant. In addition, you can include the DBSERVERNAME function in a SELECT statement to display the name of the database server where the current database resides.

You can use these functions to select an expression that consists entirely of constant values or an expression that includes column data. In the first instance, the result is the same for all rows of output.

In addition, you can use the HEX function to return the hexadecimal encoding of an expression, the ROUND function to return the rounded value of an expression, and the TRUNC function to return the truncated value of an expression. For more information on the preceding functions, see the Informix Guide to SQL: Syntax.

In Query 4-39, the LENGTH function calculates the number of bytes in the combined fname and lname columns for each row where the length of company is greater than 15.

SELECT  customer_num,

	LENGTH (fname) + LENGTH (lname)  namelength

	FROM  customer

	WHERE  LENGTH (company)  >  15 

 






 Query Result 4-39 







 

Although the LENGTH function might not be useful when you work with DB-Access or the Relational Object Manager, it can be important to determine the string length for programs and reports. The LENGTH function returns the clipped length of a CHARACTER or VARCHAR string and the full number of bytes in a TEXT or BYTE string.

Use the USER function when you want to define a restricted view of a table that contains only rows that include your user id. For information about how to create views, see the Informix Guide to Database Design and Implementation and the GRANT and CREATE VIEW statements in the Informix Guide to SQL: Syntax.

Query 4-40 returns the user name (login account name) of the user who executes the query. It is repeated once for each row in the table.

SELECT  *  FROM  cust_calls

	WHERE  user_id  =  USER 

If the user name of the current user is richc, Query 4-40 retrieves only those rows in the cust_calls table where user_id = richc, as Query Result 4-40 shows.

Query Result 4-40

The TODAY function returns the current system date. If Query 4-41 is issued when the current system date is July 10, 1998, it returns this one row.

SELECT  *  FROM  orders

	WHERE  order_date  =  TODAY 


 






 Query Result 4-41 

You can include the function DBSERVERNAME (or its synonym, SITENAME) in a SELECT statement to find the name of the database server. You can query the DBSERVERNAME for any table that has rows, including system catalog tables.

In Query 4-42, you assign the label server to the DBSERVERNAME expression and also select the tabid column from the systables system catalog table. This table describes database tables, and tabid is the table identifier.

SELECT  DBSERVERNAME  server,  tabid  
	FROM  systables

	WHERE  tabid  <=  4 

 






 Query Result 4-42 

The WHERE clause restricts the numbers of rows displayed. Otherwise, the database server name would be displayed once for each row of the systables table.

In Query 4-43, the HEX function returns the hexadecimal format of three specified columns in the customer table, as Query Result 4-43 shows.

SELECT  HEX (customer_num)  hexnum,  HEX  (zipcode)  hexzip,  

	HEX  (rowid)  hexrow  

	FROM  customer

 






 Query Result 4-43 

For information about rowids, see Using Rowid Values In SELECT Statements.

You can use the DBINFO function in a SELECT statement to find any of the following information:

• The name of a dbspace corresponding to a tablespace number or expression
• The last serial value inserted in a table
• The number of rows processed by selects, inserts, deletes, updates, and execute routine statements
• The session ID of the current session
• The name of the host computer on which the database server runs
• The exact version of the database server to which a client application is connected

You can use the DBINFO function anywhere within SQL statements and within SPL routines.

Query 4-44 shows how you might use the DBINFO function to find out the name of the host computer on which the database server runs.

SELECT DBINFO('dbhostname')
	FROM systables

	WHERE tabid = 1 

 






 Query Result 4-44 

Without the WHERE clause to restrict the values in the tabid, the host name of the computer on which the database server runs would be repeated for each row of the systables table. Query 4-45 shows how you might use the DBINFO function to find out the complete version number and the type of the current database server.

SELECT DBINFO('version','full')
	FROM systables

	WHERE tabid = 1

For more information about how to use the DBINFO function to find information about your current database server, database session, or database, see the Informix Guide to SQL: Syntax.

You can use the DECODE function to convert an expression of one value to another value. The DECODE function has the following form:

DECODE(test, a, a_value, b, b_value, ..., n, n_value, exp_m )

DECODE returns a_value when a equals test, and returns b_value when b equals test, and, in general, returns n_value when n equals test.

If several expressions match test, DECODE returns n_value for the first expression found. If no expression matches test, DECODE returns exp_m; if no expression matches test and there is no exp_m, DECODE returns NULL.

The DECODE function does not support arguments of type TEXT or BYTE.

Suppose an employee table exists that includes emp_id and evaluation columns. Suppose also that execution of Query 4-46 on the employee table returns the rows that Query Result 4-46 shows.

SELECT emp_id, evaluation 
	FROM employee

 






 Query Result 4-46 

In some cases, you might want to convert a set of values. For example, suppose you want to convert the descriptive values of the evaluation column in the preceding example to corresponding numeric values. Query 4-47 shows how you might use the DECODE function to convert values from the evaluation column to numeric values for each row in the employee table.

SELECT emp_id, DECODE(evaluation, "poor", 0, "fair", 25, 
"good", 50, "very good", 75, "great", 100, -1) AS evaluation 
	FROM employee 

 






 Query Result 4-47 

You can specify any data type for the arguments of the DECODE function provided that the arguments meet the following requirements:

• The arguments test, a,b, ..., n all have the same data type or evaluate to a common compatible data type.
• The arguments a_value, b_value, ..., n_value all have the same data type or evaluate to a common compatible data type.

You can use the NVL function to convert an expression that evaluates to null to a value that you specify. The NVL function accepts two arguments: the first argument takes the name of the expression to be evaluated; the second argument specifies the value that the function returns when the first argument evaluates to null. If the first argument does not evaluate to null, the function returns the value of the first argument. Suppose a student table exists that includes name and address columns. Suppose also that execution of Query 4-48 on the student table returns the rows that Query Result 4-48 shows.

SELECT name, address 
	FROM student

 






 Query Result 4-48 

Query 4-49 includes the NVL function, which returns a new value for each row in the table where the address column contains a null value.

SELECT name, NVL(address, "address is unknown") AS address 
	FROM student 

 






 Query Result 4-49 

You can specify any data type for the arguments of the NVL function provided that the two arguments evaluate to a common compatible data type.

If both arguments of the NVL function evaluate to null, the function returns null.