(I posted this earlier but I left the subject blank)
Postgresql version 7.0.3 on Linux Mandrake 7.2
I've had a look through the lists and can't find any answers to the
following problem.
I have a view which uses 3 subselects (if more info is required I've
included the my SQL and the view that is created at the end). When I create
the view the PostgreSQL creates the subselect with two fields (I guess it's
the group by clause that may be causing this). The view works fine even
though when I use \d to describe it, it looks like it returns more than one
field - although it doesn't). However when I use pg_dump to back it up and
then recreate the db I get an error that the subselect must only have one
field. It doesn't take too long to hand edit the dump file and get it to be
created correctly but I'd sooner the backup worked automatically and not
have to worry about it.
Any advice anyone can give would be very much appreciated.
Regards,
Tim Knowles
(I've marked with a * two sections,one from my original and one from the
view created for comparison purposes)
MY SQL
SELECT a.quotationnumber, a.phasenumber, a.revisionnumber, a.itemnumber,
a.customerdiscount, b.description, b.ourpartnumber, b.mfrpartnumber, b.unit,
b.commoditycode, b.volumem3, b.weightkg, (SELECT float8(sum(b.quantity)) AS
float8 FROM qte_tbl_quoteitems b WHERE (((b.quotationnumber =
a.quotationnumber) AND (b.revisionnumber = b.revisionnumber)) AND
(b.itemnumber = a.itemnumber)) GROUP BY b.itemnumber) AS quantity,
*
(SELECT float8(sum(("numeric"(b.quantity) *
round("numeric"((((b.basecurrencyprice * (1 - (b.discount / 100))) * (1 +
(b.markup / 100))) * (1 - (b.customerdiscount / 100)))), 2)))) AS float8
FROM qte_tbl_quoteitems b WHERE (((b.quotationnumber = a.quotationnumber)
AND (b.revisionnumber = a.revisionnumber)) AND (b.itemnumber =
a.itemnumber)) GROUP BY b.itemnumber) AS sum,
*
(SELECT float8(sum(round("numeric"((((b.basecurrencyprice * (1 - (b.discount
/ 100))) * (1 + (b.markup / 100))) * (1 - (b.customerdiscount / 100)))),
2))) AS float8 FROM qte_tbl_quoteitems b WHERE (((b.quotationnumber =
a.quotationnumber) AND (b.revisionnumber = a.revisionnumber)) AND
(b.itemnumber = a.itemnumber)) GROUP BY b.itemnumber) AS
unitpriceafterdiscount, (SELECT
float8(sum(round("numeric"(((b.basecurrencyprice * (1 - (b.discount / 100)))
* (1 + (b.markup / 100)))), 2))) AS float8 FROM qte_tbl_quoteitems b WHERE
(((b.quotationnumber = a.quotationnumber) AND (b.revisionnumber =
a.revisionnumber)) AND (b.itemnumber = a.itemnumber)) GROUP BY b.itemnumber)
AS unitpricebeforediscount FROM qte_tbl_quoteitems a, gen_tbl_products b
WHERE (b.ourpartnumber = a.ourpartnumber) GROUP BY a.quotationnumber,
a.phasenumber, a.revisionnumber, a.itemnumber, a.customerdiscount,
b.description, b.ourpartnumber, b.mfrpartnumber, b.unit, b.commoditycode,
b.volumem3, b.weightkg;
SQL FROM THE VIEW THAT HAS BEEN CREATED
SELECT a.quotationnumber, a.phasenumber, a.revisionnumber, a.itemnumber,
a.customerdiscount, b.description, b.ourpartnumber, b.mfrpartnumber, b.unit,
b.commoditycode, b.volumem3, b.weightkg, (SELECT float8(sum(b.quantity)) AS
float8, b.itemnumber FROM qte_tbl_quoteitems b WHERE (((b.quotationnumber =
a.quotationnumber) AND (b.revisionnumber = b.revisionnumber)) AND
(b.itemnumber = a.itemnumber)) GROUP BY b.itemnumber) AS quantity,
(SELECT float8(sum(("numeric"(b.quantity) *
round("numeric"((((b.basecurrencyprice * (1 - (b.discount / 100))) * (1 +
(b.markup / 100))) * (1 - (b.customerdiscount / 100)))), 2)))) AS float8,
b.itemnumber FROM qte_tbl_quoteitems b WHERE (((b.quotationnumber =
a.quotationnumber) AND (b.revisionnumber = a.revisionnumber)) AND
(b.itemnumber = a.itemnumber)) GROUP BY b.itemnumber) AS sum,
(SELECT float8(sum(round("numeric"((((b.basecurrencyprice * (1 - (b.discount
/ 100))) * (1 + (b.markup / 100))) * (1 - (b.customerdiscount / 100)))),
2))) AS float8, b.itemnumber FROM qte_tbl_quoteitems b WHERE
(((b.quotationnumber = a.quotationnumber) AND (b.revisionnumber =
a.revisionnumber)) AND (b.itemnumber = a.itemnumber)) GROUP BY b.itemnumber)
AS unitpriceafterdiscount, (SELECT
float8(sum(round("numeric"(((b.basecurrencyprice * (1 - (b.discount / 100)))
* (1 + (b.markup / 100)))), 2))) AS float8, b.itemnumber FROM
qte_tbl_quoteitems b WHERE (((b.quotationnumber = a.quotationnumber) AND
(b.revisionnumber = a.revisionnumber)) AND (b.itemnumber = a.itemnumber))
GROUP BY b.itemnumber) AS unitpricebeforediscount FROM qte_tbl_quoteitems a,
gen_tbl_products b WHERE (b.ourpartnumber = a.ourpartnumber) GROUP BY
a.quotationnumber, a.phasenumber, a.revisionnumber, a.itemnumber,
a.customerdiscount, b.description, b.ourpartnumber, b.mfrpartnumber, b.unit,
b.commoditycode, b.volumem3, b.weightkg;
Disclaimer:
1. This email is strictly confidential to the person to whom it has been sent. If you believe you have received this
emailin error please contact Ametco International Limited on (020) 8963 1888 or email postmaster@ametco.co.uk
2. Any views expressed in this email are the views of the author, not of Ametco International Ltd..
