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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
