title: oracle query with inconsistent results
body: <p>Im having a very strange problem, i have a complicated view that returns incorrect data when i query on a particular column.<br>
  heres an example:</p>
  
  <pre><code>select empname
         , has_garnishment 
  from timecard_v2 
  where empname = 'Testerson, Testy';
  </code></pre>
  
  <p>this returns the single result 'Testerson, Testy', 'N'</p>
  
  <p>however, if i use the query:</p>
  
  <pre><code>select empname
         , has_garnishment 
  from timecard_v2 
  where empname = 'Testerson, Testy'
  and has_garnishment = 'Y';
  </code></pre>
  
  <p>this returns the single result 'Testerson, Testy', 'Y'</p>
  
  <p>The second query should return a subset of the first query, but it returns a different answer.</p>
  
  <p>When I use the query:</p>
  
  <pre><code>select empname
         , has_garnishment 
  from timecard_v2 
  where empname = 'Testerson, Testy'
  and has_garnishment = 'N';
  </code></pre>
  
  <p>I get no results</p>
  
  <p>I have dissected the view and determined that this section of the view definition is where the problem arises and the problem exists even if i take the sql definition and run it as a straight query(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required):</p>
  
  <pre><code>
  SELECT 
    e.fullname empname ,
    NVL2(ded.has_garn, 'Y', 'N') has_garnishment
  FROM timecard tc ,
    orderdetail od ,
    orderassign oa ,
    employee e ,
    employee3 e3 ,
    customer10 c10 ,
    order_misc om,
    (SELECT COUNT(*) has_garn,
      v_ssn
    FROM deductions
    WHERE yymmdd_stop                             = 0
    OR (LENGTH(yymmdd_stop)                       = 7
    AND to_date(SUBSTR(yymmdd_stop, 2), 'YYMMDD') > sysdate)
    GROUP BY v_ssn
    ) ded
  WHERE oa.lrn(+) = tc.lrn_order
  AND om.lrn(+)   = od.lrn
  AND od.orderno  = oa.orderno
  AND e.ssn       = tc.ssn
  AND c10.custno  = tc.custno
  AND e.lrn       = e3.lrn
  AND e.ssn       = ded.v_ssn(+)
  </code></pre>
  
  <p>One thing of note about the definition of the 'ded' subquery.  The v_ssn field is a virtual field on the deductions table.</p>
  
  <p>I am not a DBA im a software developer but we recently lost our DBA and the new one is still getting up to speed so im trying to debug this issue.  That being said, please explain things a little more thoroughly then you would for a fellow oracle expert.</p>
  
  <p>thanks</p>