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_AssembliesM issingInfo 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 PackagePricingG roupID is null then '' else 'Package Not in a Pricing Group. ' end || case when (PackagePricing GroupID is not null And BasePrice is null) Or PricePerPin is null then 'Group Prices are Not Configured' else '' end AS Problem
FROM qry_AssembliesM issingInfo1 a LEFT JOIN
/*Here is the virtual table I mentioned using select * on a join*/
(select * from PackagePricingG roups b Inner JOIN PricingGroups c ON b.PricingGroupI D = c.PricingGroupI D) groups
ON a.PackageID = groups.PackageI D AND a.SupplierID = groups.Supplier ID
WHERE (a.NumPins Is Null AND groups.BasePric e Is Null) OR (groups.BasePri ce Is Null AND groups.PricePer Pin Is Null)
OR (coalesce(Price PerPin,0)>0 and coalesce(NumPin s,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 PackagePricingG roupID is null then '' else 'Package Not in a Pricing Group. ' end || case when (PackagePricing GroupID is not null And BasePrice is null) Or PricePerPin is null then 'Group Prices are Not Configured' else '' end, groups.BasePric e, groups.PricePer Pin;
2) This is what PGAdmin shows after I have created view:
-- View: "qry_assemblies missinginfo"
-- DROP VIEW qry_assembliesm issinginfo;
CREATE OR REPLACE VIEW qry_assembliesm issinginfo 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 packagepricingg roupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) ||
CASE
WHEN packagepricingg roupid IS NOT NULL AND baseprice IS NULL OR priceperpin IS NULL THEN 'Group Prices are Not Configured'::te xt
ELSE ''::text
END AS problem
FROM qry_assembliesm issinginfo1 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 packagepricingg roupid, pricinggroupid, packageid, createuserid, createdate, modifyuserid, modifydate, pricinggroupid, description, supplierid, baseprice, priceperpin, currencyid, createuserid, createdate, modifyuserid, modifydate
FROM packagepricingg roups b
JOIN pricinggroups c ON b.pricinggroupi d = c.pricinggroupi d) groups ON a.packageid = groups.packagei d AND a.supplierid = groups.supplier id
WHERE a.numpins IS NULL AND groups.basepric e IS NULL OR groups.basepric e IS NULL AND groups.priceper pin IS NULL OR COALESCE(pricep erpin, 0::double precision) > 0::double precision AND COALESCE(numpin s, 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 packagepricingg roupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) ||
CASE
WHEN packagepricingg roupid IS NOT NULL AND baseprice IS NULL OR priceperpin IS NULL THEN 'Group Prices are Not Configured'::te xt
ELSE ''::text
END, groups.basepric e, groups.priceper pin;
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.c o.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,f ieldc 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.c o.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,f ieldc 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
