Special cases of Orcaleand logical operators

In special cases of Orcaleand logical operators, when writing Oracle statements, we may add and logical operators after where statements.

In special cases of Orcale and logical operators, when we write an Oracle statement, we may add and logical operators after the where statement.

Some days ago, when I was working on a project, I encountered a very strange situation. Today I plan to write it down for my friends to share.

When writing an Oracle statement, we may add the and logical operator behind the where statement, but there is a problem. Column

Select (t1.SaleCount/t1.PurductCount) as s from table1 t1

Where

T1.PurductCount> 0

From the above SQL statement, there is no problem. I actually divide the sales volume of the product by the total volume of the product to get the sales rate above.

When the PurductCount field is not 0, the query will not have any problems, but if the PurductCount has 0. Of course there will be problems. The denominator cannot be 0.

Some may say that you are an SB. didn't you use the where statement to filter it out?

Yes, this is a special case. Let's talk about the situation of this and logical operator in orcale (mssql is not clear). If there is an and SQL statement, What Is orcale execution,

Find the result set first, and then directly perform the and logical operation. To put it bluntly, the and operation is executed later in the query. In this case, what will happen.

The program will report the error that the denominator cannot be 0. Before the and operation, the data in our database may be zero, but the division operation is performed when we query the results.

How can this problem be solved? There are many methods,

The simplest one is to use the decode function to filter out the queried field values with 0 or special characters if the denominator is 0,

Then, add the where condition for filtering this special character.

I wrote it for the first time, but I kept it for myself.

As for what you can't see, you can't understand me.