wrong returns

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Tue, 20 Nov 2007 13:10:38 -0500
Message-ID: <4743231E.2090304@tufts.edu>

Hi,

We have one sql statement ran in two databases, both in 10g, one returns as following;

ID_NUMBER  Sort_Name                                                    Club


---------- ------------------------------------------------------------ 

----------------------------------------

0000110534 SMALL,JONATHAN,A.
Fletcher Fund President's Circle
0000110535 SMALL,CORNELIA,M.
Fletcher Fund President's Circle

In prod database, we get the same two rows come back, but the club column one is "NULL", it should return with the same value as above.

ID_NUMBER  Sort_Name                                                    Club


---------- ------------------------------------------------------------ 

----------------------------------------

0000110534 SMALL,JONATHAN,A.
Fletcher Fund President's Circle
0000110535 SMALL,CORNELIA,M. Question: both databases returns the right rows, but one column didn't return the value it should be. The db ran different execution plans, both db have very close dataset and same optimizer parameters setting. colud someone can shed some light why this could be wrong...

The sql statement is;
SELECT DISTINCT e.id_number

   2                ,e.pref_name_sort "Sort_Name"
   3                 ,gc.club_description "Club"
   4   FROM entity e
   5       ,tu_gift_totals tgt
   6       ,entity_record_type ert
   7       ,address pref
   8       ,(SELECT g.gift_club_id_number
   9               ,g.gift_club_status
  10               ,gct.club_description
  11           FROM gift_clubs      g
  12               ,gift_club_table gct
  13          WHERE g.gift_club_code = gct.club_code
  14            AND substr(g.gift_club_end_date, 1, 4) = '2007'
  15            AND g.gift_club_status = 'L'
  16            AND g.school_code = 'FL'
  17            AND 0 = (SELECT COUNT(*)
  18                       FROM gift_clubs      zz
  19                           ,gift_club_table gctz
  20                      WHERE zz.gift_club_code = gctz.club_code
  21                        AND zz.gift_club_id_number = 
g.gift_club_id_number
  22                        AND zz.gift_club_status = 'L'
  23                        AND zz.school_code = 'FL'
  24                        AND substr(zz.gift_club_end_date, 1, 4) = '2007'
  25                        AND gctz.club_year_type > gct.club_year_type)
  26         ) gc

  27 WHERE
  28 --Joins
  29 e.id_number = tgt.id_number(+)

  30  AND e.id_number = ert.id_number
  31  AND e.id_number = pref.id_number
  32  AND e.id_number = gc.gift_club_id_number(+)

  33 AND pref.addr_pref_ind = 'Y'
  34 AND pref.addr_status_code = 'A'
  35 AND e.record_status_code = 'A'
  36 --Input Criteria

  37  AND ert.school_code = 'FL'
  38  AND tgt.school(+) = 'FL'
  39  AND ert.class_year = '1968'

  40 --Fletcher Class Year
  41 AND
  42 ( ert.record_type_code = 'AL'
  43 OR

  44      (ert.record_type_code = 'GA' AND
  45      ert.id_number NOT IN (SELECT z.id_number
  46                                FROM entity_record_type z
  47                               WHERE z.record_type_code = 'AL'
  48                                 AND z.school_code = 'FL'))
  49   OR
  50      (ert.record_type_code = 'ST' AND ert.class_year = '2007')

  51 )
  52 -- Part of test to limit to the Small Household   53 AND e.pref_name_sort like '%SMALL%'   54 ORDER BY id_number;

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 20 2007 - 12:10:38 CST