oracle - remove rows from table having full outer join condition -
i have used full outer join condition in joining query joining 2 tables , sample code below
select * from( select to_char(round(col11)) today1,to_char(round(col12)) today2 t1 full outer join select to_char(round(col21)) yes1,to_char(round(col22)) yes2 t2 ) main main.today1<>0 , main.today2<>0 , main.yes1<>0 , main.yes2<>0
sample output expected below
today1 today2 yes1 yes2 somevalue somevalue null null null null value somevalue somevalue somevalue somevalue value 0 0 0 0
i trying remove rows having zero values using above clause output doubles , row 0 values apperas. can know going wrong. appreciated.
trying remove rows having 0 values
use:
and not ( main.today1=0 , main.today2=0 , main.yes1=0 , main.yes2=0 )
which can transformed, using de morgan's laws:
===> https://en.wikipedia.org/wiki/de_morgan%27s_laws
into:
and ( not main.today1=0 or not main.today2=0 or not main.yes1=0 or not main.yes2=0 )
which can further simplified to:
and ( main.today1<>0 or main.today2<>0 or main.yes1<>0 or main.yes2<>0 )
note: if want null values, must use is null
operator:
where ( main.today1<>0 or main.today1 null ) , ( main.today2<>0 or main.today2 null ) , ( main.yes1<>0 or main.yes1 null ) , ( main.yes2<>0 or main.yes2 null )