0

I have a few tables in an Oracle database linked by id - fk_id, if I do:

select RAP.ID_PCT_LUCRU,SES_RAP.AN,
        TIS.CANT as c1,'R12' as modc1,
        TIT.CANT as c2,'D13' as modc2,
        TIMB.CANT as c3,'D8' as modc3,
        TIFC.CANT as c4,
        TIA.CANT as c5
     from ch_trat cht
        full outer join raportor rap on cht.fk_id_raportor = rap.id
        inner join sr ses_rap on ses_rap.id = CHT.FK_ID_SR
        full outer join sortare tis on TIS.FK_TRAT_ID = CHT.ID
        full outer join transfer tit on TIT.FK_TRAT_ID = CHT.ID
        full outer join mec_bio timb on TIMB.FK_TRAT_ID = CHT.ID
        full outer join fiz_chm tifc on TIFC.FK_TRAT_ID = CHT.ID
        full outer join altele tia on TIA.FK_TRAT_ID = CHT.ID

and the result is something like this:

ID_PCT_LUCRU    AN     c1    modc1  c2   modc2  c3   modc3  c4  c5
275382          2013         R12         D13         D8     
280933          2013         R12    222  D13         D8         44
295740          2013         R12         D13         D8    111  73
297089          2013         R12         D13    333  D8         124
281137          2013         R12         D13         D8    222  19
281137          2013         R12    222  D13         D8         123
281137          2013         R12         D13         D8         14
301410          2013   5103  R12         D13    444  D8         880

So I'd want that the result doesn't show me the first row with no value on c1,c2,c3,c4,c5. I know I should use COALESCE but I don't know how to use it in this case. Many Thanks!

Improve this question

1 Answer 1

Reset to default
1

You can make it a subquery:

select * from (...) a
where coalesce(a.c1, coalesce(a.c2, coalesce(a.c3, coalesce(a.c4, coalesce(a.c5, null))))) is not null

You just put your query at the dots.

EDIT:

You just put where coalesce(TIS.CANT, TIT.CANT, TIMB.CANT, TIFC.CANT, TIA.CANT) is not null as a where clause in your query, as Andriy said. :)

Improve this answer
1
  • 4
    This is how I would probably do it too. No need to nest COALESCE like that, however – just specify all the arguments in a single call: coalesce(a.c1, a.c2, a.c3, a.c4, a.c5) is not null would work just as well. Using a subquery in general would make sense but in this case seems unnecessary. This is because the c1, c2 etc. aliases are assigned to columns rather than expressions, and so you could apply COALESCE to the original column names and thus avoid the subselect.
    – Andriy M
    Jul 9, 2015 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.