Hi,

I have PostgreSQL 8.0.3 for Windows XP and i have in attach the database
with problem.

The problem occour with this SQL

select proextra.data as data, 0 as codigo, proextra.motivo as nome, 'X'
as tipo, proextra.qtd, 0 as valor_u, 0 as cod_v from proextra where
proextra.data>=to_date('28/04/2006','dd/mm/yyyy') and
proextra.data<=to_date('28/04/2006','dd/mm/yyyy') and
proextra.lancado='S' and proextra.codigo=1106 union
select entrad1.emissao as data, entrad1.cod_f as codigo, entrad1.nome_f
as nome, 'E' as tipo, entrad2.qtd, entrad2.preco_u as valor_u, 0 as
cod_v from entrad1, entrad2 where entrad1.numero=entrad2.numero and
entrad1.emissao>=to_date('28/04/2006','dd/mm/yyyy') and
entrad1.emissao<=to_date('28/04/2006','dd/mm/yyyy') and
entrad1.lancado='S' and entrad2.cod_p=1106

The result correct is two (2) rows in table proextra but result one (1)
row!!!

If i do:

select proextra.data as data, 0 as codigo, proextra.motivo as nome, 'X'
as tipo, proextra.qtd, 0 as valor_u, 0 as cod_v from proextra where
proextra.data>=to_date('28/04/2006','dd/mm/yyyy') and
proextra.data<=to_date('28/04/2006','dd/mm/yyyy') and
proextra.lancado='S' and proextra.codigo=1106
I receive two (2) rows if i union with other table i receive one (1) row!!!

This is a bug or my SQL is not correct????

For create this database

create database pcpneus;
create user loja001 with password 'test';
psql pcpneus "postgres" < pcpneus.txt



Thanks,


Dario Basso Cardoso
Brazil

Ps. How i send backup for this database for analize?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dario Basso Cardoso <cardosodario (AT) itelefonica (DOT) com.br> writes:
You do know that UNION is defined to eliminate duplicate output rows?
(UNION ALL doesn't eliminate duplicates, and might be what you want.)

If that's not what you're seeing then you need to provide more info.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thank you

With UNION ALL the SQL work correct for my case...

Dario.

Tom Lane escreveu:


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly