Decode (divisor, 0, show 0%, not zero normal expression) instead of the original that there is a possibility of 0 of the expression in addition to the error
Resolution: Select Decode (b,0,0,a/b*100) per from AA;
(1) The meaning of decode
Meaning explanation:
Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)
The meaning of the function is as follows:
IF condition = value 1 THEN
return (translation value 1)
elsif condition = value 2 THEN
Return (translation value 2)
......
elsif condition = value N THEN
return (translation value N)
ELSE
return (default value)
End IF
Decode (Operation of field or field, value 1, value 2, value 3)
The result of this function is that when the value of the operation of a field or field equals 1 o'clock, the function returns a value of 2, otherwise the value 3 is returned.
Of course, a value of 1, a value of 2, and a value of 3 can also be an expression, which makes some SQL statements a lot simpler (2) decode use
1. Compare size
Select decode(sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function returns 0, 1, and 1, depending on whether a value is 0, positive, or negative.
For example:
Variable 1=10, variable 2=20
Then sign (variable 1-variable 2) returns-1, thedecode decoding result is "Variable 1", which achieves the purpose of taking a smaller value
2.
Combined with the Lpad function, how to automatically add 1 to the value of the primary key and make up 0 in front
Select Lpad (Decode (count (record number), 0,1,max (To_number (record number) +1)), 14, ' 0 ') record number from Tetdmis
eg
Select Decode (dir,1,0,1) from A1_interval
The value of dir is 1 to 0, and 0 to 1.
For example, I want to inquire about the number of boys and girls in a class is how much?
Usually we write this:
Select COUNT (*) from table where gender = male;
Select COUNT (*) from table where gender = female;
It's too much to be a union to show it together.
With decode, you just need a word.
Select sum (decode (sex, male, 1,0)), sum (decode (sex, female, 1,0)) from table
eg
Select SUM (Decode (Siteno, ' LT ', 1,0)), SUM (Decode (Siteno, ' SZ ', 1,0)) from facd605;
Select sum (case Siteno when ' LT ' then 1 else 0 "), sum (case Siteno when ' SZ ' then 1 else 0") from facd605;