Oracle SQL: Return null value when no listagg result
<p>I am using the following script to return some basic info. the script returns 65 lines (as expected)...</p>

<pre><code>select unique
       trunc(li.cre_dat) cre_date,
       li.cre_usr,
       li.catnr,
       li.av_part_no,
       li.artist,
       li.title,
       li.prodtyp,
       li.packtyp,
       nvl(sp.name_for_customer,sp.name) pack_type 
from   leos_item li,
       scm_packtyp sp
where  li.cunr in ('816900','816901','816902')
and    li.item_type = 'FP'
and    li.av_part_no is null
and    trunc(li.cre_dat) &gt;= '01-JAN-2016'
and    li.model_force_creation_idc != 'Y'
and    li.i_status != 'I'
and    li.packtyp = sp.packtyp
</code></pre>

<p>...but, when I add Listagg in to my selects, the report only returns 55 lines. 10 lines does not have a listagg result therefore are omitted from the results...</p>

<pre><code>select unique
       trunc(li.cre_dat) cre_date,
       li.cre_usr,
       li.catnr,
       li.av_part_no,
       li.artist,
       li.title,
       li.prodtyp,
       li.packtyp,
       nvl(sp.name_for_customer,sp.name) pack_type,
       regexp_replace(listagg(nvl(bom.av_part_no,'No'), ', ')
         within group (order by bom.item_id),'([^,]+)(,\1)+', '\1') masters
from   leos_item li,
       scm_packtyp sp,
       TABLE(leos_flatbom_pkg.GetFlatBOM(li.item_id)) bom,
       leos_item li1 
where  li.cunr in ('816900','816901','816902')
and    li.item_type = 'FP'
and    li.av_part_no is null
and    trunc(li.cre_dat) &gt;= '01-JAN-2016'
and    li.model_force_creation_idc != 'Y'
and    li.i_status != 'I'
and    li.packtyp = sp.packtyp
and    bom.item_id = li1.item_id
and    li1.item_type = 'MT'
group by li.cre_dat,
         li.cre_usr,
         li.catnr,
         li.av_part_no,
         li.artist,
         li.title,
         li.prodtyp,
         li.packtyp,
         nvl(sp.name_for_customer,sp.name)
</code></pre>

<p>But, I also need to see these lines. is there a way to return the 10 lines where no listagg result is found. I have tried the following combinations of nullif and nvl but with no luck;</p>

<pre><code>nullif(regexp_replace(listagg(nvl(bom.av_part_no,'No'), ', ') within group (order by bom.item_id),'([^,]+)(,\1)+', '\1'),'No Master')

nvl(regexp_replace(listagg(nvl(bom.av_part_no,'No'), ', ') within group (order by bom.item_id),'([^,]+)(,\1)+', '\1'),'No Master')
</code></pre>