Merge two Select

Asked

Viewed 8,195 times

0

I am developing a report in Powerbuilder and would like to know how I can join results of two SQL queries in the report. If I add the f_bloqueto_nosso_numero function in the first querie and add the invoice table in the from , the records multiply in the report so I wanted to leave a querie only to bring our number 


Ex : 
  Nome  Nosso Numero
  José      007
  José      007
  José      007

I believe this occurs because v_collection is a View that contains data from the Invoice table.

However I can not select directly in the invoice table because it does not have all the data for the report , already the View data has several tables.


select v_arrecadacao.tipo,
       v_arrecadacao.idregiao,
       v_arrecadacao.idbairro,
       v_arrecadacao.idrota,
       v_arrecadacao.sequencia_leit,
       v_arrecadacao.iduc,
       v_arrecadacao.idclasse,
       v_arrecadacao.refaturamento,
       v_arrecadacao.retificado,
       v_arrecadacao.numero_reaviso,
       v_arrecadacao.nome,
       v_arrecadacao.numero_fiscal,
       v_arrecadacao.ano_mes,
       v_arrecadacao.ano_mes_original,
       v_arrecadacao.tipo_calculo,
       v_arrecadacao.data,
       v_arrecadacao.datahora,

           v_arrecadacao.empresas_nome,
        v_arrecadacao.idagente,
        v_arrecadacao.agentearrecadador_nome,
        v_arrecadacao.datahora,
        v_arrecadacao.valor_recebido as valor_recebido,
        v_arrecadacao.situacao,
        v_arrecadacao.valor_diferenca,

f_bloqueto_nosso_numero(33,3265,invoice.tipo_calculo,invoice.idcodfactura,invoice.vcto,invoice.iduc,invoice.ano_mes,13000617) as nosso_numero, nvl((select uc.on from uc Where uc.iduc = v_storage.iduc),’S') as connected, nvl((select uc.idmotivodeslig from uc Where uc.iduc = v_arrecadacao.iduc),0) as idmotivodeslig

from v_collection, invoice

Where v_collection.date BETWEEN :pagto_ini AND :pagto_final
and v_fundraising.motivo_low = 1

UNION

select personal invoice, invoice.vcto , invoice.calculus type, invoice.id invoice, invoice.ano_mes , f_bloqueto_nosso_numero(33,3265,invoice.tipo_calculo,invoice.idcodfactura,invoice.vcto,invoice.iduc,invoice.ano_mes,13000617) as nosso_numero

from invoice

Where invoice.iduc = v_collection.iduc

sql oracle pl-sql
  • 1

    What is the difference between the queries? Would an OUTER JOIN not dismiss Union ? For a UNION to be made the numbers and the types of columns must match, the queries must be "homogeneous", only those who know the business can say which columns apply a Union.

  • @Motta, I solved the problem, I had not entered the primary keys of View and table in WHERE, so the records multiplied in the report view.

1 answer

2


  • Vlw by the reference @Dias Hugs.

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.