How to filter NULL from the macro output

Database
Enthusiast

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.

Tags (1)
8 REPLIES
Teradata Employee

Re: How to filter NULL from the macro output

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!

Enthusiast

Re: How to filter NULL from the macro output

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.

Senior Apprentice

Re: How to filter NULL from the macro output

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.

Enthusiast

Re: How to filter NULL from the macro output

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

Senior Apprentice

Re: How to filter NULL from the macro output

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.

Enthusiast

Re: How to filter NULL from the macro output

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

Senior Apprentice

Re: How to filter NULL from the macro output

You can only filter on NULLs using IS [NOT] NULL.

But WHERE e.id IS NOT NULL? is the same as a Left Join.

Enthusiast

Re: How to filter NULL from the macro output

Thanks Dnoeth. :)