|
Posted by octangle on 06/06/06 21:45
Upon further review...
As written in the previous post, records with a TaxableAmount of 0 will
not be found to be reversed... so in an attempt to remedy this I
modified the LEFT OUTER JOIN as follows:
FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
This succeeds at finding the 0 TaxableAmount records... but once a
companion reversal record is inserted into the database both records
(the original and a the reversal) are found on subsequent queries using
this technique... since these records are retrieved by the query as
records to reverse, these get reversed again (thus making a total of 4
instances of the original record - instead of 2 which is all are
needed). And if we repeat the process a 0 TaxableAmount record will
redouble it instances every time the process is run...
Now the questions are...
Can the above LEFT OUTER JOIN be fixed?
OR
Should 0 TaxableAmounts be processed in their own pass with their own
query (yuck)?
OR
Is the original query really better because it works for all
TaxableAmounts despite the fact that its 13% slower....
OR
Is there another option????
Navigation:
[Reply to this message]
|