Re: sql giving unpredictable results

From: William Beilstein <BeilstWH_at_OBG.com>
Date: Tue, 29 Aug 2000 11:34:59 -0400
Message-Id: <10603.115743@fatcity.com>

See the following script to do the mass compile of invalid objects.

=AF----------------------------------------
set hea off pages 0 lines 79 verify off echo off

spool comp_all.tmp

select

    decode( OBJECT_TYPE, 'PACKAGE BODY',     'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',     'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' = compile;'
)
from

    dba_objects a, sys.order_object_by_dependency b where

     A.OBJECT_ID =3D B.OBJECT_ID(+) and
    STATUS =3D 'INVALID' and
    OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',

                      'TRIGGER', 'VIEW' )

order by

    DLEVEL DESC,
    OBJECT_TYPE,
    OBJECT_NAME; spool off

@comp_all.tmp
=AF-------------------------------------

>>> <MTPConsulting_at_aol.com> 08/29/00 11:33AM >>> Just to follow up on this, specifically, what you want to do in this type = of=20
case is always include the criteria on your main select in the subquery. = Try=20
this:

2A. SELECT * from ps_stdnt_test_comp where emplid =3D  '100000303' and

        test_component =3D 'MATH' and test_id =3D 'SAT I'
        and score =3D (select max(ax16.score)from ps_stdnt_test_comp
 ax16 where
        ax16.emplid =3D '100000303'
            and test_component=3D'MATH' and test_id =3D 'SAT I');

That should work fine. Now, for performance reasons, you may want to = modify=20
this slightly to make it into a correlated subquery:

2B. SELECT * from ps_stdnt_test_comp ax16 where emplid =3D '100000303' = and

        test_component =3D 'MATH' and test_id =3D 'SAT I'
        and score =3D (select max(score)from ps_stdnt_test_comp where
        emplid =3D ax16.emplid
            and test_component=3Dax16.emplid and test_id =3D ax16.test_id);=

That might be slightly faster for you. Either query should work fine = though.

Marc Perkowitz
MTP Systems Consulting, Ltd.

In a message dated 8/29/2000 9:15:41 AM Central Daylight Time, carle_at_att.co= m=20
writes:

<< Hi,
=20

     I'm going to assume a little bit about your table. I assume each = emplid
 has a MATH score and a VERB score. If this is true, the way you have your  first SQL statement coded will only provide results for whichever score = is
 higher. For example, if the MATH score is 95 and the VERB score is 87, = your
 subselect will translate to WHERE score =3D 95. In that case, the VERB = version
 of your statement will not be satisfied.=20

     The second way you have of coding it isn't the same as the first. In  that one, you're just getting the max score for an emplid and MATH. That  will work regardless of how many records you have for MATH. I hope this  makes sense.
=20

 Bill
=20

        -----Original Message-----
        From:   Donna Belden [mailto:dbelden_at_URI.EDU]=20
        Sent:   Tuesday, August 29, 2000 8:12 AM
        To: ORACLE-L_at_lists.sunysb.edu=20
        Subject:    sql giving unpredictable results


=20

        We are running into a situation where we are getting
 unpredictable results
        when executing a SQL statement is a certain way.


=20

        1. SELECT * from ps_stdnt_test_comp  where emplid =3D
 '100000303' and
        test_component =3D 'VERB' and test_id =3D 'SAT I'
        and score =3D (select max(ax16.score)from ps_stdnt_test_comp
 ax16 where
        ax16.emplid =3D '100000303');


=20

        When this statement it executed we get good results (1 row

 returned).
=20

        2. SELECT * from ps_stdnt_test_comp where emplid =3D  '100000303' and

        test_component =3D 'MATH' and test_id =3D 'SAT I'
        and score =3D (select max(ax16.score)from ps_stdnt_test_comp
 ax16 where
        ax16.emplid =3D '100000303');


=20

        When this statement is executed we get 0 rows returned.  The
 only difference
        is changing the test_component from VERB to MATH.


=20

        3.  When we recode the sql statement to this:


=20

        select max(ax16.score) from ps_stdnt_test_comp ax16 where
 ax16.emplid =3D
        '100000303' and test_component =3D 'MATH'


=20

         we get 1 row returned as we should.


=20

        4. The recoding of the first SQL to


=20

        select max(ax16.score) from ps_stdnt_test_comp ax16 where
 ax16.emplid =3D
        '100000303' and test_component =3D 'MATH'


=20

         we get 1 row returned as we should.

=20
=20

        Any suggesting on how or who to contact to resolve a  situation like this.

        Thanks
 --=20
 Author: Carle, William T (Bill), NBSO
   INET: wcarle_at_att.com=20
  >>
--=20
Author:=20
  INET: MTPConsulting_at_aol.com=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Tue Aug 29 2000 - 10:34:59 CDT