Extend method creating additional empty rows

February 11, 2024

I am using .extend on a master table object (mastertbl) and looping so it grabs the objects from 3 different temp tables (with different object data type cols). I noticed that it is creating empty rows, for example, only 1 of the tables has data and the other 2 tables doesn’t. So when I return mastertbl, it has 3 empty rows. How do you prevent it from adding empty rows?

   For list_obj in (select coalesce(tab.name,tab1.name,tab2.name) name, 
    tab1.location location, tab2.eduhist eduhist, tab.purchases purchases
    from tab full join tab1 on tab1.name = tab.name
     full join tab2 on tab2.name = tab1.name)
   Loop var_comp.extend;
   n := n+1
   var_listc(n) := mastertbl(list_obj.name, list_obj.location, list_obj.eduhist, list_obj.purchases)
   End loop;

Output

                             tab1            tab2            tab
        Name(varchar2)  LOCATION(obj)   EDUHIST(obj)    PURCHASES(obj)   
    1   Carol           Some_object                 
    2   -------------------------EMPTY ROW---------------------------
    3   -------------------------EMPTY ROW---------------------------
    4   -------------------------EMPTY ROW---------------------------

>Solution :

Your query does not work as you try to SELECT tab3.purchases but you never join to a tab3 table.

I assume that you are getting the NULL values because you have values in the tab table but no corresponding values in tab1, tab2 or tab3. Or maybe a name in tab3 but no value in the purchases column. Without some sample data that replicates the problem and a syntactically valid query it is difficult to tell.

If your sample data appears to match that case then, check that at least one of the OUTER JOINed columns IS NOT NULL:

SELECT mastertbl(
         COALESCE(tab.name, tab1.name, tab2.name),
         tab1.location,
         tab2.eduhist,
         tab.purchases
       )
BULK COLLECT INTO var_listc
FROM   tab 
       FULL OUTER JOIN tab1
       ON tab.name = tab1.name
       FULL OUTER JOIN tab2
       on tab.name = tab2.name
 WHERE  tab.name IS NOT NULL
 OR     tab1.name IS NOT NULL
 OR     tab2.name IS NOT NULL;