OOps. Didn't send it to the list. (There has to be a better way of
doing this then always having to remember to change the recipient.)
I'm sorry, I thought I described the problem pretty clearly.
Here is the actual queries with comment annotations where the problem
occurred.
1) This is the query that I typed in:
create or replace view qry_AssembliesMissingInfo as
SELECT a.AssemblyID, a.AssemblyName, a.PackageID, a.Package, a.SupplierID, a.NumPins, case when numpins is null then '' else 'Num Pins Not Specified. ' end || case when PackagePricingGroupID is null then '' else 'Package Not in a Pricing Group. ' end || case when (PackagePricingGroupID is not null And BasePrice is null) Or PricePerPin is null then 'Group Prices are Not Configured' else '' end AS Problem
FROM qry_AssembliesMissingInfo1 a LEFT JOIN
/*Here is the virtual table I mentioned using select * on a join*/
(select * from PackagePricingGroups b Inner JOIN PricingGroups c ON b.PricingGroupID = c.PricingGroupID) groups
ON a.PackageID = groups.PackageID AND a.SupplierID = groups.SupplierID
WHERE (a.NumPins Is Null AND groups.BasePrice Is Null) OR (groups.BasePrice Is Null AND groups.PricePerPin Is Null)
OR (coalesce(PricePerPin,0)>0 and coalesce(NumPins,0)=0)
GROUP BY a.AssemblyID, a.AssemblyName, a.PackageID, a.Package, a.SupplierID, a.NumPins, case when numpins is null then '' else 'Num Pins Not Specified. ' end || case when PackagePricingGroupID is null then '' else 'Package Not in a Pricing Group. ' end || case when (PackagePricingGroupID is not null And BasePrice is null) Or PricePerPin is null then 'Group Prices are Not Configured' else '' end, groups.BasePrice, groups.PricePerPin;
2) This is what PGAdmin shows after I have created view:
-- View: "qry_assembliesmissinginfo"
-- DROP VIEW qry_assembliesmissinginfo;
CREATE OR REPLACE VIEW qry_assembliesmissinginfo AS
SELECT a.assemblyid, a.assemblyname, a.packageid, a.package, a.supplierid, a.numpins, (
CASE
WHEN numpins IS NULL THEN ''::text
ELSE 'Num Pins Not Specified. '::text
END ||
CASE
WHEN packagepricinggroupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) ||
CASE
WHEN packagepricinggroupid IS NOT NULL AND baseprice IS NULL OR priceperpin IS NULL THEN 'Group Prices are Not Configured'::text
ELSE ''::text
END AS problem
FROM qry_assembliesmissinginfo1 a
/*Here is where the problem comes in as you can see there are a
number of fields with the same name, such as pricinggroupid,
createuserid... */
LEFT JOIN ( SELECT packagepricinggroupid, pricinggroupid, packageid, createuserid, createdate, modifyuserid, modifydate, pricinggroupid, description, supplierid, baseprice, priceperpin, currencyid, createuserid, createdate, modifyuserid, modifydate
FROM packagepricinggroups b
JOIN pricinggroups c ON b.pricinggroupid = c.pricinggroupid) groups ON a.packageid = groups.packageid AND a.supplierid = groups.supplierid
WHERE a.numpins IS NULL AND groups.baseprice IS NULL OR groups.baseprice IS NULL AND groups.priceperpin IS NULL OR COALESCE(priceperpin, 0::double precision) > 0::double precision AND COALESCE(numpins, 0) = 0
GROUP BY a.assemblyid, a.assemblyname, a.packageid, a.package, a.supplierid, a.numpins, (
CASE
WHEN numpins IS NULL THEN ''::text
ELSE 'Num Pins Not Specified. '::text
END ||
CASE
WHEN packagepricinggroupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) ||
CASE
WHEN packagepricinggroupid IS NOT NULL AND baseprice IS NULL OR priceperpin IS NULL THEN 'Group Prices are Not Configured'::text
ELSE ''::text
END, groups.baseprice, groups.priceperpin;
3) Exactly what happened.
My view worked fine after I created it.
I did a Backup using PGAdmin (which uses pg_dump)
I did a restore to a new database using PGAdmin (which uses PG_restore)
The restore gave me the ambiguous error I mentioned before
on that view.
I took the PGAdmin version of my view and saw that it had
extrapolated the fields from * and that was causing the
ambiguity. I then changed it back to * and executed the
create or replace view statement.
The view now works again.
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
__________________________________________________ ______________________________
Sim Zacks <si*@compulab.co.il> writes:
I just did a dump and restore of my database and one of my views did
not recreate.
The error received was :
pg_restore.exe: [archiver (db)] could not execute query: ERROR: column reference "pricinggroupid" is ambiguous
I checked the function in the original database, using PGAdmin, and
the system seemed to have slightly changed my query.
Part of my query was a virtual table (i.e joining on (select *
from ...) as tablename ) and the system changed that to:
(select fielda,fieldb,fieldc from...) the problem is that there were
joined tables in that virtual table with identical fieldnames.
The view creation should have failed to begin with, then. Could we see
the *exact* problem and not a handwavy description?
regards, tom lane
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
__________________________________________________ ______________________________
Sim Zacks <si*@compulab.co.il> writes: I just did a dump and restore of my database and one of my views did
not recreate.
The error received was :
pg_restore.exe: [archiver (db)] could not execute query: ERROR: column reference "pricinggroupid" is ambiguous
I checked the function in the original database, using PGAdmin, and
the system seemed to have slightly changed my query.
Part of my query was a virtual table (i.e joining on (select *
from ...) as tablename ) and the system changed that to:
(select fielda,fieldb,fieldc from...) the problem is that there were
joined tables in that virtual table with identical fieldnames.
The view creation should have failed to begin with, then. Could we see
the *exact* problem and not a handwavy description?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
