display a value only if none of the dependent values are nul-CodePudding

I am quite new to SQL and would need some suggestions to write a query to select the value of Col1 only if none of the value of status of Col2 (Col3) is null in plsql. In the above, I am expecting the result to return only A2 as in A1, there is a null value in col3.

CodePudding user response：

There are many ways to skin this cat but this should return you all the A2 rows:

select *
from yourTable d 
where d.col1 in (select col1
                 from (select col1
                             ,sum(case when col3 is null then 1 else 0 end) null_values
                       from yourTable
                       group by col1
                      )
                 where null_values = 0
                )

To explain the approach briefly: The inner query counts the number of null col3 values for each col1 value; then the middle query returns the only the distinct col1 values having no associated null col3 values, and the outer query returns the all detail for all rows containing the col1 value.

The above is SQL of course. As you have specified PL/SQL, you could include something like a cursor or the bulk collect into clause as desired to work with the query result set.

As mentioned in my comment below, this is just one way of achieving the output - Oracle analytic functions may also prove helpful. Also depending on the size of your data set, you may want to consider the performance aspect of your chosen method.