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 IBM Informix: Guide to SQL Syntax.
In Figure 238, 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
customer_num namelength
101 11
105 13
107 11
112 14
115 11
118 10
119 10
120 10
122 12
124 11
125 10
126 12
127 10
128 11Although the LENGTH function might not be useful when you work with DB–Access, 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 IBM Informix: Database Design and Implementation Guide and the GRANT and CREATE VIEW statements in the IBM Informix: Guide to SQL Syntax.
Figure 240 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, Figure 240 retrieves only those rows in the cust_calls table where user_id = richc, as Figure 241 shows.
customer_num 110
call_dtime 1998-07-07 10:24
user_id richc
call_code L
call_descr Order placed one month ago (6/7) not received.
res_dtime 1998-07-07 10:30
res_descr Checked with shipping (Ed Smith). Order sent yesterday-we
were waiting for goods from ANZ. Next time will call with
delay if necessary
customer_num 119
call_dtime 1998-07-01 15:00
user_id richc
call_code B
call_descr Bill does not reflect credit from previous order
res_dtime 1998-07-02 08:21
res_descr Spoke with Jane Akant in Finance. She found the error and is
sending new bill to customerThe TODAY function returns the current system date. If Figure 242 is issued when the current system date is July 10, 1998, it returns this one row.
SELECT * FROM orders WHERE order_date = TODAY
order_num 1018 order_date 07/10/1998 customer_num 121 ship_instruct SW corner of Biltmore Mall backlog n po_num S22942 ship_date 07/13/1998 ship_weight 70.50 ship_charge $20.00 paid_date 08/06/1998
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 Figure 244, 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
server tabid montague 1 montague 2 montague 3 montague 4
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 Figure 246, the HEX function returns the hexadecimal format of two columns in the customer table, as Figure 247 shows.
SELECT HEX (customer_num) hexnum, HEX (zipcode) hexzip FROM customer
hexnum hexzip 0x00000065 0x00016F86 0x00000066 0x00016FA5 0x00000067 0x0001705F 0x00000068 0x00016F4A 0x00000069 0x00016F46 0x0000006A 0x00016F6F
·
·
·
You can use the DBINFO function in a SELECT statement to find any of the following information:
You can use the DBINFO function anywhere within SQL statements and within SPL routines.
Figure 248 shows how you might use the DBINFO function to find out the name of the host computer on which the database server runs.
SELECT FIRST 1 DBINFO('dbhostname') FROM systables
(constant) lyceum
Without the FIRST 1 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. Figure 250 shows how you might use the DBINFO function to find out the complete version number and the type of the current database server.
SELECT FIRST 1 DBINFO('version','full') FROM systablesFor more information about how to use the DBINFO function to find information about your current database server, database session, or database, see the IBM 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 Figure 251 on the employee table returns the rows that Figure 252 shows.
SELECT emp_id, evaluation FROM employee
emp_id evaluation 012233 great 012344 poor 012677 NULL 012288 good 012555 very good
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. Figure 253 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
emp_id evaluation 012233 100 012344 0 012677 -1 012288 50 012555 75
·
·
·
You can specify any data type for the arguments of the DECODE function provided that the arguments meet the following requirements:
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 Figure 255 on the student table returns the rows that Figure 256 shows.
SELECT name, address FROM student
name address John Smith 333 Vista Drive Lauren Collier 1129 Greenridge Street Fred Frith NULL Susan Jordan NULL
Figure 257 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
name address John Smith 333 Vista Drive Lauren Collier 1129 Greenridge Street Fred Frith address is unknown Susan Jordan address is unknown
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.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]