Search code examples
sqloracleplsqloracle-apexoracle-apex-5.1

Merge two table with different columns without null values

again for here, to ask help - i'm working with two Oracle tables

I have this problem (image-1)

enter image description here

having two table with different column, i made a union all and i get the result that you see on the image-1 This is my query:

(select td.DETALLE col1, 
           tg.FININ col2, 
           tg.ffinn col3,
           null as col4
    from table_1 tg
    inner join other_table td on (tg.cod_f = td.fol and tg.prod_c = td.prod)
    where UPPER(tg.sys_user) = UPPER(:APP_USER)
      and td.fol = :P10_FOLLETO
      and td.prod = :P10_PRODUCTO
      and tg.fin = :P10_FECHA_INICIO)
    union all 
    (select null as col1
          ,null as col2
          ,null as col3
          ,camp.camp_util.get_data(:P10_FOLLETO, :P10_PRODUCTO, :P10_FECHA_INICIO) as col4 -- This is a FUNCTION from another schema that will return in this case just MAD
    from dual);

but my result mut be:

enter image description here

Note: every query will return a single row, is for that reason that i want to group all in one row.

Con somebody help me?

Regards

Solution

  • I think that you don't want union, but a single select with a call to the function:

    select td.DETALLE col1, 
       tg.FININ col2, 
       tg.ffinn col3,
       camp.camp_util.get_data(:P10_FOLLETO, :P10_PRODUCTO, :P10_FECHA_INICIO) as col4
from table_1 tg
inner join other_table td on (tg.cod_f = td.fol and tg.prod_c = td.prod)
where UPPER(tg.sys_user) = UPPER(:APP_USER)
  and td.fol = :P10_FOLLETO
  and td.prod = :P10_PRODUCTO
  and tg.fin = :P10_FECHA_INICIO