Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Oracle SQL exclude rows when coloumns are empty

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading