DECODE () and NVL () functions of SQL statements

SQL statement DECODE () and NVL () function usage SELECT DECODE (WP01.ONDO _ KBN, 0 ,'?? For example, when '1, 'normal temperature ', 2, and 'cold recovery') AS ONDO_KBNFROM WP01_S_HAITOTAL WP01 // analysis: When WP01.ONDO _ KBN = 0 "?? When WP01.ONDO _ KBN = 1, assign "Normal Temperature" to "Normal Temperature". When WP01.ONDO _ KBN = 2, assign "cold recovery" to NVL () function: NVL (ARG, if the previous arg value is NULL, the returned VALUE is the following VALUE. Use DECODE (NVL (M01.NINUSI _ NM ,''),'','-', m01.NINUSI _ NM)
// Analysis: first, determine if M01.NINUSI _ NM is empty. if it is null, assign a null value to the [NVL function]. Next, judge whether the retrieved field is null. If so, assign it to '-'. The final processing is M01.NINUSI by default. NM. The DECODE function www.2cto.com is an exclusive function provided by Oracle. It is a powerful function. Although it is not the standard of SQL, it is very useful for performance. Up to now, other database vendors have not yet provided DECODE-like functions, and some database vendors have even criticized Oracle's SQL standards. In reality, such criticism is somewhat one-sided or inadequate. Just as some carriage manufacturers complained about Henry. Ford's carriage is not standard. 1 if-then-else logic in DECODE is often used in logic programming for logical judgment. In DECODE syntax, this is actually the logic processing process. Its syntax is as follows: www.2cto.com DECODE (value, if1, then1, if2, then2, if3, then3 ,... else) Value indicates any column of any type in a table or any result obtained by calculation. When each value is tested, if the value is if1, the result of the Decode function is then1; if the value is if2, the result of the Decode function is then2; and so on. In fact, multiple if/then pairs can be provided. If the value result is not equal to any given pairing, the Decode result returns else. Note that if, then, and else can both be functions or computing expressions. Description: DECODE (condition, value 1, translation value 1, value 2, translation value 2 ,... value n, translation value n, default value) the meaning of this function is as follows: IF condition = value 1 THENRETURN (translation value 1) ELSIF condition = value 2 THENRETURN (translation value 2 )...... ELSIF condition = value n THENRETURN (translation value n) ELSERETURN (default value) end if 2 DECODE simple example there are many data dictionaries in the Oracle system designed using decode, for example, the V $ SESSION data dictionary view that records SESSION information is like this. We learned from Oracle8i/9i Reference that after a user logs on successfully, the corresponding records of the user are displayed in V $ SESSION, however, the command operations performed by the user only record the code of the command in this view (0-no operation, 2-Insert ...), Instead of the specific command keyword. Therefore, when we need to know the names of users and their operations, we need to use the following command to obtain detailed results: www.2cto.com select sid, serial #, username, DECODE (command, 0, 'none', 2, 'insert', 3, 'select', 6, 'update', 7, 'delete', 8, 'drop', 'other ') cmks and from v $ session where username is not null; 3 DECODE implements table transpose. the table in the database is a two-dimensional table consisting of columns and rows. The number of columns is usually limited in any database, and the number of rows changes greatly. If the table is large, the number of rows may be larger than 10 million rows. Different rows in the same column may have different values, and they are not pre-defined. Example: housing provident fund report replacement instance: 1. each organization opens an account at the local administrative bank. An account is to register the basic information and employee information of the organization. 2. every month, the accountant of each organization submits the housing provident fund of all employees of the Unit to the handling bank. The system records the payment details of each employee and records the codes of the handling bank on each record; 3. each month, quarter, half year, and year-end, you are required to change the handling line to "column" to provide a detailed report for the month: handling line: chengxi district Chengdong district month: 2001.01 xxxx1.xx xxxxx2.xx 2001.02 xxxx3.xx xxxxx4.xx www.2cto.com the original data sequence is: chengxi district 2001.01 xxxxx1.xx Chengdong district 2001.01 xxxxx2.xx chengxi district 2001.02 xxxxx3.xx Chengdong district 2001.02 xxxxx4.xx housing provident fund system records the employee's pay-as-you-go every month. The detailed pay_lst table structure is: bank_code varchar2, -- acc_no varchar2 (15) not null, -- unit code (unit account) emp_acc_no varchar2 (20) not null, -- employee account tran_date date not null, -- payment date tran_val Number (7,2) not null, -- payment amount sys_date date default sysdate, -- system date oper_id varchar2 (10) -- operator code
In this table structure, it is easy to count rows as rows, but if you want to change rows to columns) it is difficult to output such a format. If you use the DECODE function for processing, it becomes very simple: www.2cto.com we create a view to query the current pay_lst table. Change the code of the handling line to the specific name of the handling line: create or replace view bank_date_lst ASSelect to_char (tran_date, 'yyyy. mm '), SUM (DECODE (bank_code, '001', tran_val, 0) chengxi district, SUM (DECODE (bank_code, '002', tran_val, 0) Chengnan district, SUM (DECODE (bank_code, '003 ', tran_val, 0) FROM pay_lstGROUP BY to_char (tran_date, 'yyyy. mm '). After a view is created, you can directly query the view to display the results by column. The nvl NVL function provides a simple but useful function. If you give it a null value at any time, it returns a value of your choice. This ability to automatically replace null values helps provide more comprehensive output. The syntax of the NVL function is as follows: NVL (input_source, result_if_input_value_is_null) Where input_source is generally a column name. Result_if_input_value_is_null can be any value: direct value (hard encoding), reference to other columns, or expression. Note: The NVL function is not actually a value in the update table. The original data remains unchanged. NVL has A strange feature. It requires that the data types of input_source and result_if_input_value_is_null be the same. If you want this function to display the popular "N/A" when detecting null values, problems will occur. Because "N/A" is text, if input_value is A text column, there is no problem. However, if a null value is found in a date or value column, you need to apply the TO_CHAR function to the input_value column so that input_value can also become text. If NVL is NULL, take the specified value for example: nvl (yanlei777, 0)> 0NVL (yanlei777, 0). This means that if yanlei777 is NULL, obtain the total value of a field through the query. If the value is null, a default value is given, for example: select nvl (sum (t. dwxhl), 1) from tb_jhde t indicates that if sum (t. dwxhl) = NULL returns 1 another useful method related to declare I integerselect nvl (sum (t. dwxhl), 1) into I from tb_jhde t where zydm =-1 so that the obtained total value can be stored in variable I, if the queried value is null, set its value to select nvl (rulescore, 0) from zwjc_graderule where rulecode = 'fwtd 'in the default 1 www.2cto.com orcale '; if the record does not contain data with rulecode = 'fwtd. no data is found. select nvl (rulescore, 0) into rule_score from zwjc_graderule where rulecode = 'fwtd '; select nvl (sum (rulescore), 0) from zwjc_graderule where rulecode = 'fwtd '; if the record does not contain rulecode = 'fwtd' data. you can also obtain data with a column name of nvl (rulescore, 0) and a value of 0. select nvl (sum (rulescore), 0) into rule_score from zwjc_graderule where rulecode = 'fwtd '; no error is reported