>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
