Re: variable not found in subplan target list

On 11/1/2011 3:54 PM, Roger Niederland wrote:
> Hello,
>
> I stripped down the original query to what is below. I am not saying
> that the query below
> is useful except to show an error I am getting in Postgresql 9.1.1 on
> both SL6.1 (64 bit) and
> Windows 2008 server 9.1.1 (32-bit and 64-bit). The error I am
> getting is:
>
> ERROR: variable not found in subplan target list
> SQL state: XX000
>
> If I remove the inner join to person (at the end) to works on 9.1.1
>
> I tried this query on Postgresql 9.1.0 on Windows Vista (32-bit) and
> it works.
> Also on the postgres 8.4 it works on both OS's.
>
> Thanks,
> Roger
> (sorry if this is double posted, I sent this in about 24 hours ago,
> but it was stalled
> due to me not subscribing to the list, I turned into a better citizen
> and subscribed)
>
>
> SELECT
> COALESCE(FreshUps.employeeId, Appts.employeeId) AS employeeId
> FROM
> (
> SELECT
> COALESCE(Ups.employeeId, -1) AS employeeId
> FROM
> (
> SELECT Lead.salesmanId AS employeeId
> FROM Lead
> GROUP BY Lead.salesmanID) AS Ups
> ) AS FreshUps
> FULL OUTER JOIN
> (
> SELECT
> COALESCE(Ups.employeeId, -1) AS employeeId
> FROM
> (
> SELECT SalesAppointment.SalesmanID AS employeeID
> FROM SalesAppointment
> GROUP BY SalesAppointment.SalesmanID) AS Ups
> ) AS Appts USING (employeeId)
>
> INNER JOIN Employee USING (employeeid)
> INNER JOIN Person ON Employee.employeeId = Person.personId
>

-----------------------------------------
I extracted the tables from the database which generates the error above

I eliminated most of the columns such that this query still exhibits
this behavior to reduce
the file size. I have a zipped file from windows postgresql 9.1.1
(32-bit) which is about 1.5MBytes.
I verified that the database generated from this file will produce the
error.

Thanks,
Roger