Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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

0
hidrau
Asked:
hidrau
  • 3
1 Solution
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now