I have this oracle sql query which selects several columns containing values:
SELECT DISTINCT
name,
age,
weight,
height
FROM
my_table
I want to exclude rows from the report that contain no values but they should be reported if they have 0.00 as a value.
EDIT: I add another case that I missed, the rows should be only excluded if all of them contain no value. Row d should be in the report.
Example:
| name | age | weight | height |
|---|---|---|---|
| a | 10 | 20 | 105 |
| b | 0 | 0 | 0 |
| c | |||
| d | 15 | 110 |
After the SQL statement, this should be reported:
| name | age | weight | height |
|---|---|---|---|
| a | 10 | 20 | 105 |
| b | 0 | 0 | 0 |
| d | 15 | 110 |
>Solution :
It should be:
SELECT DISTINCT
name,
age,
weight,
height
FROM
my_table
WHERE LENGTH(age) > 0 or LENGTH(weight) > 0 OR LENGTH(height) > 0
OR age is not null or weight is not null or height is not null
If the data type of the columns is numeric, you can simply check for NULL values only.
SELECT DISTINCT
name,
age,
weight,
height
FROM
my_table
WHERE age is not null or weight is not null or height is not null