Total number of columns in Oracle that meet certain Column Conditions

Recently, a query statement is required in the project report to count the number of other columns that meet the conditions of a certain column.

For example, the following table structure is available:

 

You need to add a column after column D to count the number of columns B, C, and D that are not empty when column A is not empty.

After self-testing and help searching, we finally realized the above query.

First, let's talk about the usage of several functions:

 Null: Indicates a null value or an invalid value.
 Nvl (expr1, expr2)

Parameter description: If expr1 is null, expr2 is returned. If not null, expr1 is returned. Note that the two types must be consistent.
 Nvl2 (expr1, expr2, expr3)

Parameter description: If expr1 is not null, expr2 is returned; if it is null, expr3 is returned. If the expr2 and expr3 types are different, expr3 will be converted to the expr2 type.
 Nullif (expr1, expr2)

Parameter description: if equal, return NULL, not equal to return expr1

 

The following is the result of my implementation in two ways:

Method 1:

Code

1 Select A,
2 B,
3 C,
4 D,
5 Case
6 When A Is   Not   Null   Then
7 Nvl2 (B, 1 , 0 ) + Nvl2 (C, 1 , 0 ) + Nvl2 (D, 1 , 0 )
8 Else
9 0
10 End E
11 From AA

Method 2:

Code

1 Select A,
2 B,
3 C,
4 D,
5 Case
6 When A Is   Not   Null   Then
7 Decode (B, '' , 0 , 1 ) + Decode (C, '' , 0 , 1 ) + Decode (D, '' , 0 , 1 )
8 Else
9 0
10 End E
11 From AA

 

Display:

 

If you have other good methods, please contact us ~