Re: Executing wrong sql

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 10 Oct 2008 19:52:13 +0200
Message-ID: <48EF964D.30508@roughsea.com>

Sandra Becker wrote:

                    SELECT DISTINCT mi.docid, mi.invnumber, mi.datereceived
                    FROM invpayment ip, mbinv mi
                    WHERE ip.invnumber = mi.invnumber
                      AND mi.boid = incoid
                      AND substr(mi.soid, 0, 12) = substr(vndr.partnerid, 0, 12)
                      AND mi.ISDELETED != '1'
                      AND mi.state = 'Imported'
                      AND mi.datereceived BETWEEN inStartDate AND nvl(inEndDate, add_months(inStartDate, 6));

Sandra,

First of all, AFAIK the first character in an SQL string is numbered 1, not 0 - this isn't a C char *.
It's not impossible that it confuses the optimizer. I suspect that the optimizer takes slightly different paths in different environments, and that you get a proper result somewhere and a wrong one elsewhere.
Besides, it always sadden me to see a DISTINCT before a series of columns that in all likelihood contain the primary key. I'd push the DISTINCT to a subquery against invpayment, either in the where clause or as an inline view; and the 'vndr' table is referenced in your query but missing, I'm afraid you have sanitized too hard ...

 

-- http://www.freelists.org/webpage/oracle-l Received on Fri Oct 10 2008 - 12:52:13 CDT