RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'

2012-04-20 Thread Svein Erling Tysvær 
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

RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'

2012-04-20 Thread Rick Debay 
 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?

No.  In all cases for each RPL_PO_ITM.NDC (RPL_PO_ITM is a child of
RPL_PO) there must be a matching RPL_POBILL_ITM.NDC (RPL_POBILL_ITM is a
child of RPL_POBILL).  Also for each RPL_POBILL_ITM.NDC there must be a
matching RPL_PO_ITM.NDC.  The NULL COST and PRICE is to see if there is
nothing on the other side of the left join.
In short, all children of both related parents (there is a joke there
somewhere) must match exactly on NDC. 


Disclaimer: This message (including attachments) is confidential and may be 
privileged. If you have received it by mistake please notify the sender by 
return e-mail and delete this message from your system. Any unauthorized use or 
dissemination of this message in whole or in part is strictly prohibited. 
Please note that e-mails are susceptible to change. RxStrategies, Inc. shall 
not be liable for the improper or incomplete transmission of the information 
contained in this communication or for any delay in its receipt or damage to 
your system. RxStrategies, Inc. does not guarantee that the integrity of this 
communication has been maintained nor that this communication is free from 
viruses, interceptions or interference.

[firebird-support] NOT EXISTS returns 'no current record for fetch operation'

2012-04-19 Thread Rick Debay 
This query returns 'no current record for fetch operation'

SELECT po.ID, pb.ID
FROM
  RPL_PO po
  JOIN RPL_POBILL pb
ON pb.PO = po.ID
WHERE
  po.ID = ? AND
  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
  )

But these running the queries separately works.

SELECT po.ID, pb.ID
FROM
  RPL_PO po
  JOIN RPL_POBILL pb
ON pb.PO = po.ID
WHERE
  po.ID = ?

/* plug in values returned above */
SELECT *
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

Disclaimer: This message (including attachments) is confidential and may be 
privileged. If you have received it by mistake please notify the sender by 
return e-mail and delete this message from your system. Any unauthorized use or 
dissemination of this message in whole or in part is strictly prohibited. 
Please note that e-mails are susceptible to change. RxStrategies, Inc. shall 
not be liable for the improper or incomplete transmission of the information 
contained in this communication or for any delay in its receipt or damage to 
your system. RxStrategies, Inc. does not guarantee that the integrity of this 
communication has been maintained nor that this communication is free from 
viruses, interceptions or interference.

RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'

2012-04-19 Thread Rick Debay 
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
  ) 


Disclaimer: This message (including attachments) is confidential and may be 
privileged. If you have received it by mistake please notify the sender by 
return e-mail and delete this message from your system. Any unauthorized use or 
dissemination of this message in whole or in part is strictly prohibited. 
Please note that e-mails are susceptible to change. RxStrategies, Inc. shall 
not be liable for the improper or incomplete transmission of the information 
contained in this communication or for any delay in its receipt or damage to 
your system. RxStrategies, Inc. does not guarantee that the integrity of this 
communication has been maintained nor that this communication is free from 
viruses, interceptions or interference.

RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'

2012-04-19 Thread Leyne, Sean 
Rick,

 In case anyone is confused

Count me as one of them!

 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.
 

What happens with this query?

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 poi1
LEFT JOIN RPL_POBILL_ITM pbi1 ON poi1.NDC = pbi1.NDC AND pbi1.INVOICE = 
pb.ID
WHERE 
  Poi1.PURCH_ORDER = po.ID
  AND pbi1.COST IS NULL
  )
  AND NOT EXISTS (
SELECT 1
FROM RPL_POBILL_ITM pbi2
LEFT JOIN RPL_PO_ITM poi2 ON poi2.NDC = pbi2.NDC AND poi2.PURCH_ORDER = 
po.ID
WHERE 
  Pbi2.INVOICE = pb.ID
  AND poi2.PRICE IS NULL
  ) 

And this?

SELECT po.ID, pb.ID
FROM RPL_PO po
  JOIN RPL_POBILL pb ON (pb.PO = po.ID)
  LEFT JOIN RPL_PO_ITM poi1 ON (poi1.PURCH_ORDER = po.ID)
  LEFT JOIN RPL_POBILL_ITM pbi1 ON ((pbi1.NDC = poi1.NDC)  AND (pbi1.INVOICE = 
pb.ID))
  LEFT JOIN RPL_POBILL_ITM pbi2 ON (pbi2.INVOICE = pb.ID)
  LEFT JOIN RPL_PO_ITM poi2 ON ((poi2.NDC = pbi2.NDC) AND (poi2.PURCH_ORDER = 
po.ID))
WHERE
  AND pbi1.COST IS NULL
  AND poi1.NDC IS NULL
  AND poi2.NDC IS NULL
  AND poi2.PRICE IS NULL


Sean