>In case anyone is confused as to what I'm trying to accomplish, it's an outer join on line
>items where both sides match with no NULL items.
>If my approach is poor, then I won't have to worry about question about the exception.
>
>SELECT po.ID, pb.ID
>FROM
> RPL_PO po
> JOIN RPL_POBILL pb
> ON pb.PO = po.ID
>WHERE
> NOT EXISTS (
> SELECT 1
> FROM RPL_PO_ITM poi
> LEFT JOIN RPL_POBILL_ITM pbi
> ON poi.NDC = pbi.NDC AND pbi.INVOICE = pb.ID
> WHERE poi.PURCH_ORDER = po.ID
> AND pbi.COST IS NULL
> ) AND
> NOT EXISTS (
> SELECT 1
> FROM RPL_POBILL_ITM pbi
> LEFT JOIN RPL_PO_ITM poi
> ON pbi.NDC = poi.NDC AND poi.PURCH_ORDER = po.ID
> WHERE pbi.INVOICE = pb.ID
> AND poi.PRICE IS NULL
> )

Well, yes, I'm a bit confused, though I blame it mostly on not knowing more about your system. Instinctively, I'd say your query ought to work, although I've never before seen a NOT EXISTS which links to an outer table in the RIGHT side of a LEFT JOIN and haven't thought all too much about it.

I think it is likely that your query could be improved. Am I right in assuming that you want there in ALL cases where there is an RPL_PO_ITM to also exist at least one RPL_POBILL_ITM record and that all matching RPL_POBILL_ITM must have COST defined?

Regardless of the answer to the above question, I hope this query does the same as your query and hopefully works (assuming that it is the LEFT JOIN that gives you the error):

SELECT po.ID, pb.ID
FROM
RPL_PO po
JOIN RPL_POBILL pb
ON pb.PO = po.ID
WHERE
NOT EXISTS (
SELECT 1
FROM RPL_PO_ITM poi
LEFT JOIN RPL_POBILL_ITM pbi
ON poi.NDC = pbi.NDC
WHERE poi.PURCH_ORDER = po.ID
AND pbi.COST IS NULL
AND (pbi.INVOICE = pb.ID
OR pbi.INVOICE IS NULL)
) AND
NOT EXISTS (
SELECT 1
FROM RPL_POBILL_ITM pbi
LEFT JOIN RPL_PO_ITM poi
ON pbi.NDC = poi.NDC
WHERE pbi.INVOICE = pb.ID
AND poi.PRICE IS NULL
AND (poi.PURCH_ORDER = po.ID
OR poi.PURCH_ORDER IS NULL)
)

I'm curious whether this works, so please report back. Also, if this is a Firebird error (i.e. that your query returns such an error regardless of how it is executed and that there no stupid thing about this query that we simply fail to see) and that it occurs in new Firebird versions, then it ought to be reported so that it can be fixed.

HTH,
Set