union query returning duplicates

[Sim Zacks]

I am using 8.0 beta 1 on an RH 8 Linux server.

I have a union query that I am converting from access (where it
worked) and it is returning duplicates. The only difference between
the two rows is the Row field, which is returned automatically.

and an example of a row that it has returned duplicate. I have
verified that the row only shows up 1 time in each select statement
when run individually.

Here is a sample of the duplicates it returned (one was row 2 and the
other row 3, but that didn't seem to come with a copy and paste from
pgadmin):
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97 ;3;6.6
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97 ;3;6.6
Below is the query:
SELECT a.assemblyid, a.assemblyname, b.fixedprice,
CASE
WHEN sum(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END AS calcprice, b.supplierid, a.productid, COALESCE(b.fixe dprice,
CASE
WHEN sum(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END) AS activeprice
FROM assemblies a
JOIN qry_assemblyfix edprices b ON a.assemblyid = b.assemblyid
LEFT JOIN qry_assemblycal cprices c ON c.supplierid = b.supplierid AND b.assemblyid = c.assemblyid
WHERE b.supplierid =97
GROUP BY a.assemblyid, a.assemblyname, b.fixedprice, b.supplierid, totalcount, a.productid
order by assemblyid
UNION
SELECT a.assemblyid, a.assemblyname, c.fixedprice,
CASE
WHEN sum(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END AS calcprice, b.supplierid, a.productid, COALESCE(c.fixe dprice,
CASE
WHEN sum(packagecoun t) <> totalcount::num eric THEN NULL::double precision
ELSE sum(calculatedp rice)
END) AS activeprice
FROM assemblies a
JOIN qry_assemblycal cprices b ON a.assemblyid = b.assemblyid
LEFT JOIN qry_assemblyfix edprices c ON c.supplierid = b.supplierid AND c.assemblyid = b.assemblyid
WHERE b.supplierid =97
GROUP BY a.assemblyid, a.assemblyname, c.fixedprice, b.supplierid, totalcount, a.productid
order by assemblyid
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Sim Zacks]

It is very weird, I just tried both a group by and distinct and both
of them still return the duplicates.

I also tried a very simple union which didn't return any duplicates,
both of these said, it is obviously not a problem with union.

I just tried the query without the case statement that does the sum
and it did work. I am wondering if there might be something about
double precision numbers (such as a weird roundoff error or something) that prevent
it from comparing it to another number.

In my example it is returning fairly simple numbers (6.6) so I don't
see where it could make a mistake.

The system automatically put in the ::double precision when I created the
View that encases the query I sent. Maybe there is a better typecast
that I should use to manually override it?

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

_______________ _______________ _______________ _______________ _______________ _____

Did you tried a select distinct?

Hagen

Sim Zacks wrote:

I am using 8.0 beta 1 on an RH 8 Linux server.

I have a union query that I am converting from access (where it
worked) and it is returning duplicates. The only difference between
the two rows is the Row field, which is returned automatically.

and an example of a row that it has returned duplicate. I have
verified that the row only shows up 1 time in each select statement
when run individually.

Here is a sample of the duplicates it returned (one was row 2 and the
other row 3, but that didn't seem to come with a copy and paste from
pgadmin):
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97 ;3;6.6
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97 ;3;6.6

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Sim Zacks]

double precision is inexact and therefore any query returning a field
of that type cannot be in a group by/distinct...

I switched it to type ::numeric(10,4) and it worked fine.

It was the system that automatically did the conversion for me, so I
will have to figure out why and keep that in mind for the next time.
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

_______________ _______________ _______________ _______________ _______________ _____

It is very weird, I just tried both a group by and distinct and both
of them still return the duplicates.

I also tried a very simple union which didn't return any duplicates,
both of these said, it is obviously not a problem with union.

I just tried the query without the case statement that does the sum
and it did work. I am wondering if there might be something about
double precision numbers (such as a weird roundoff error or something) that prevent
it from comparing it to another number.

In my example it is returning fairly simple numbers (6.6) so I don't
see where it could make a mistake.

The system automatically put in the ::double precision when I created the
View that encases the query I sent. Maybe there is a better typecast
that I should use to manually override it?

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

_______________ _______________ _______________ _______________ _______________ _____

Did you tried a select distinct?

Hagen

Sim Zacks wrote:

I am using 8.0 beta 1 on an RH 8 Linux server.

I have a union query that I am converting from access (where it
worked) and it is returning duplicates. The only difference between
the two rows is the Row field, which is returned automatically.

and an example of a row that it has returned duplicate. I have
verified that the row only shows up 1 time in each select statement
when run individually.

Here is a sample of the duplicates it returned (one was row 2 and the
other row 3, but that didn't seem to come with a copy and paste from
pgadmin):
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97 ;3;6.6
2;"486CORE-D16-F4-C66-N0-R3-S-E";6.6;5.274;97 ;3;6.6

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Alvaro Herrera Munoz]

On Wed, Oct 20, 2004 at 01:54:04PM +0200, Sim Zacks wrote:

It is very weird, I just tried both a group by and distinct and both
of them still return the duplicates.

I also tried a very simple union which didn't return any duplicates,
both of these said, it is obviously not a problem with union.

Not related to your underlying problem, but be aware that UNION does
eliminate duplicates by design, so that could explain what you are seeing
here. If you don't want it to do that, use UNION ALL instead (the same
applies to INTERSECT and EXCEPT if you ever happen to use them).

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings