All Forums Database
08 Feb 2014
How to filter NULL from the macro output

Hi All,
replace macro jugal.samples_macro(id1 integer,id2 integer,course1 varchar(20),course2 varchar(20))
as
(
Select d.id,d.course from
(sel id,course from jugal.samples1
where (id=:id1 or id=:id2)
and (course=:course1 or course=:course2))d
FULL OUTER JOIN
(Sel * from jugal.samples1 where (course=:course1 or course=:course2))e
ON d.id=e.id;
);
exec jugal.samples_macro(1,,,'mca');
When i execute the about macro im gettin a additional row as NULL. How to restrict it. N show only those results matching the parameters.

Adeel Chaudhry 773 posts Joined 04/08
08 Feb 2014

Have you executed the same query with same parameters .... do you still get the row with NULL? Its a FULL OUTER JOIN hence you will get all rows from both tables. You need to restrict dataset within a query or change the join type to get what you specificially need.
 
HTH!
 

-- If you are stuck at something .... consider it an opportunity to think anew.

09 Feb 2014

Yes Adeel the NULL values are bcoz of FULL OUTER JOIN. I did try to restrict by using case when the in the where clause, i tried partition by clause still i was getting the extra NULL result. I was able to remove the NULL values when i included a extra ON clause with ON d.id=1;. But it doesnt seem to be a proper solution. I want to use FULL OUTRE JOIN and find a possible way were teh output is without the NULL values.
Please help me  find a different logic or approach to restrict teh NULL vslues.

dnoeth 4628 posts Joined 11/04
09 Feb 2014

Can you show some example data and what you want for result (and why)?
Of course you get only NULLs when you don't have a row in d matching both 1 and 'mca' and select only columns from d.

Dieter

10 Feb 2014

Hi Dnoeth,
Below is the example:
replace macro jugal.samples_macro(id1 integer,id2 integer,course1 varchar(20),course2 varchar(20))
as
(
Select d.id,d.course from
(sel id,course from jugal.samples1
where (id=:id1 or id=:id2)
and (course=:course1 or course=:course2))d
FULL OUTER JOIN
(Sel * from jugal.samples1 where (course=:course1 or course=:course2))e
ON d.id=e.id;
);
exec jugal.samples_macro(1,,,'mca'); -- When i pass these parameter it has  to show only the matching data not the result result with NULL values.
OUTPUT:
                           id                      course
---------------------------------------------------------------
1                        1                             mca
NULL                 NULL                        NULL

dnoeth 4628 posts Joined 11/04
11 Feb 2014

Why do you use a Full Outer Join if you don't want that result?
Do a Left Join instead or add columns from e to the Select list.

Dieter

11 Feb 2014

Yes LEFT OUTER JOIN would restrict the NULL values. I am trying other possibilities to filter NULL values.

dnoeth 4628 posts Joined 11/04
11 Feb 2014

You can only filter on NULLs using IS [NOT] NULL.
But WHERE e.id IS NOT NULL? is the same as a Left Join.

Dieter

11 Feb 2014

Thanks Dnoeth. :)

You must sign in to leave a comment.