8.1. Functions in Alphabetical Order
| The following are the MySQL flow control functions.
This function allows a particular result from a list of results to be chosen based on various conditions. It is similar to the IF( ) function except that multiple conditions and results may be strung together. In the first syntax shown, value is compared to each WHEN value. The second syntax tests each condition independently, and they are not based on a single value. Both syntaxes return NULL if there is no match and no ELSE clause. Here is an example of the first syntax: SELECT CONCAT(name_first, SPACE(1), name_last) AS Client, telephone_home AS Telephone, CASE type WHEN 'RET' THEN 'Retirement Account' WHEN 'REG' THEN 'Regular Account' WHEN 'CUS' THEN 'Minor Account' END AS 'Account Type' FROM clients; This SQL statement retrieves a list of clients and their telephone numbers, along with a description of their account type. However, the account type is a three-letter abbreviation, so CASE( ) is used to substitute each type with a more descriptive name. The previous example uses the syntax in which a common parameter is evaluated to determine the possible result. The following SQL statement utilizes the other syntax for the function: SELECT CONCAT(name_last, SPACE(1), name_first) AS Prospect, CASE WHEN YEAR(NOW( )) - YEAR(birth_date) < 18 THEN 'Minor' WHEN YEAR(NOW( )) - YEAR(birth_date) > 17 < 26 THEN 'Too Young' WHEN YEAR(NOW( )) - YEAR(birth_date) > 60 THEN 'Elderly' ELSE home_telephone; END AS Telephone FROM prospects; In this example, the SQL statement analyzes a table containing a list of people that the broker might call to buy an investment. The table contains the birth dates and the telephone numbers of each prospect. The SQL statement provides the telephone numbers only for prospects aged 26 to 60 anyone younger or older would not be suitable for this particular investment. However, a message for each prospect that is disqualified is given based on the clauses of the CASE() statement.
This function returns the second argument result if condition (the first argument) is met, and the third argument is not. Note that the value of condition is converted to an integer. It will return a numeric or a string value depending on its use. As of Version 4.0.3 of MySQL, if the second or the third argument is NULL, the other non-NULL argument will be returned. SELECT clients.client_id AS ID, CONCAT(name_first, SPACE(1), name_last) AS Client, telephone_home AS Telephone, SUM(qty) AS Shares, IF( (SELECT SUM(qty * price) FROM investments, stock_prices WHERE stock_symbol = symbol AND client_id = ID ) > 100000, 'Large', 'Small') AS 'Size' FROM clients, investments WHERE stock_symbol = 'GT' AND clients.client_id = investments.client_id GROUP BY clients.client_id LIMIT 2; +------+----------------+-----------+--------+-------+ | ID | Client | Telephone | Shares | Size | +------+--------------+-------------+--------+-------+ | 8532 | Jerry Neumeyer | 834-8668 | 200 | Large | | 4638 | Rusty Osborne | 833-8393 | 200 | Small | +------+--------------+--------------+--------+------+ This SQL statement is designed to retrieve the names and telephone numbers of clients who own Goodyear stock (the stock symbol is GT), because the broker wants to call them to recommend that they sell it. The example utilizes a subquery (available as of Version 4.1) to tally the value of all the client's stocks first (not just Goodyear stock), as a condition of the IF( ) function. It does this by joining the investments table (which contains a row for each stock purchase and sale) and the stock_prices table (which contains current prices for all stocks). If the sum of the value of all stocks owned by the client (the results of the subquery) is more than $100,000, a label of Large is assigned to the Size column. Otherwise, the client is labeled Small. The broker wants to call her large clients first. Notice in the results shown that both clients own the same number of shares of Goodyear, but one has a large portfolio.
This function returns the results of the condition given in the first argument of the function if not NULL. If the condition results are NULL, the results of the expression or string given in the second argument are returned. It will return a numeric or a string value depending on the context. SELECT CONCAT(name_first, SPACE(1), name_last) AS Client, telephone_home AS Telephone, IFNULL(goals, 'No Goals Given') AS Goals FROM clients LIMIT 2; +----------------+-----------+----------------+ | Client | Telephone | Goals | +----------------+-----------+----------------+ | Janice Sogard | 835-1821 | No Goals Given | | Kenneth Bilich | 488-3325 | Long Term | +----------------+-----------+----------------+ This SQL statement provides a list of clients and their telephone numbers, along with their investment goals. If the client never told the broker of an investment goal (i.e., the goals column is NULL), the text "No Goals Given" is displayed.
This function returns NULL if the two arguments given are equal. Otherwise, it returns the value or results of the first argument. SELECT clients.client_id AS ID, CONCAT(name_first, SPACE(1), name_last) AS Client, telephone_home AS Telephone, NULLIF( (SELECT SUM(qty * price) FROM investments, stock_prices WHERE stock_symbol = symbol AND client_id = ID ), 0) AS Value FROM clients, investments WHERE clients.client_id = investments.client_id GROUP BY clients.client_id; In this example, NULL is returned for the Value column if the value of the client's stocks is 0 (i.e., the client had stocks but sold them all). If there is a value to the stocks, however, the sum of their values is displayed. This function is the same as: CASE WHEN condition1 = condition2 THEN NULL ELSE condition1; |
EAN: 2147483647
Pages: 134