Problem with Union - I need a help

Something very strange is happening in my query with UNION.

The first query doesn't return any value and it is ok

NumeroCp   RecNf_DtEmissao              RecVenNf_Vcto                    RecVeNf_Valor


The second query returns three lines and it is ok

NumeroCp   RecNf_DtEmissao              RecVenNf_Vcto                    RecVeNf_Valor
-----------     -----------------------            ----------------------- ----------------------
0                 2011-10-20 00:00:00.000    2011-10-20 00:00:00.000     5000
0                 2011-10-20 00:00:00.000    2011-10-20 00:00:00.000     10000
0                2011-10-20 00:00:00.000     2011-10-20 00:00:00.000     10000


BUT NOW

When I use UNION I got only two results:

NumeroCp   RecNf_DtEmissao              RecVenNf_Vcto                    RecVeNf_Valor
-----------     -----------------------            ----------------------- ----------------------
0                 2011-10-20 00:00:00.000    2011-10-20 00:00:00.000     5000
0                 2011-10-20 00:00:00.000    2011-10-20 00:00:00.000     10000


Why?
select NumeroCp , RecNf_DtEmissao , RecVenNf_Vcto,RecVeNf_Valor 
from recebimento_nf_venc, recebimento_nf,TipoEntrada
where  recebimento_nf_venc.recnf_sequencia =  recebimento_nf.recnf_sequencia
and REG_EA = '1389/11C' and AtualizaCpImportacao = 'S' and
TipoEntrada = Codigo and 
TipoEntrada.TipoContabil = 'F' and NumeroCp is not null 

UNION

select 0 , provisao.Vencimento , provisao.Vencimento,valor = case when taxadolar > 0 then round(saldopagto /isnull(taxadolar,1),2) else 0 end  
from provisao , OrdemCompra 
where OrdemCompra.numero = provisao.Ordemcompra
and numregea = '1389/11C'
and isnull(cancelado,'N') = 'N' 
and numregea is not null 
and saldopagto > 0

Open in new window

LVL 1
hidrauAsked:
Who is Participating?
 
Rajkumar GsSoftware EngineerCommented:
UNION returns DISTINCT records only. So that should be correct.

If you try UNION ALL instead, you should get all records (including duplicates)

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
0
 
Rajkumar GsSoftware EngineerCommented:
In your case first query is not returning any result. But second query returns, which contains duplicate records
NumeroCp   RecNf_DtEmissao              RecVenNf_Vcto                    RecVeNf_Valor
-----------     -----------------------            ----------------------- ----------------------
0                 2011-10-20 00:00:00.000    2011-10-20 00:00:00.000     10000
0                2011-10-20 00:00:00.000     2011-10-20 00:00:00.000     10000

UNION filter out this duplicate records and output only UNIQUE records

Raj
0
 
hidrauAuthor Commented:
thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.