Re: sql giving unpredictable results

From: <MTPConsulting_at_aol.com>
Date: Tue, 29 Aug 2000 10:30:20 EDT
Message-Id: <10603.115732@fatcity.com>

Just to follow up on this, specifically, what you want to do in this type of case is always include the criteria on your main select in the subquery. Try this:

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

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

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

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

        test_component = 'MATH' and test_id = 'SAT I'
        and score = (select max(score)from ps_stdnt_test_comp where
        emplid = ax16.emplid
            and test_component=ax16.emplid and test_id = 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.com writes:

<< Hi,  

     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 = 95. In that case, the VERB version  of your statement will not be satisfied.

     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.  

 Bill  

        -----Original Message-----
        From:   Donna Belden [mailto:dbelden_at_URI.EDU]
        Sent:   Tuesday, August 29, 2000 8:12 AM
        To: ORACLE-L_at_lists.sunysb.edu
        Subject:    sql giving unpredictable results
 
        We are running into a situation where we are getting
 unpredictable results
        when executing a SQL statement is a certain way.
 
        1. SELECT * from ps_stdnt_test_comp  where emplid =


 '100000303' and

        test_component = 'VERB' and test_id = 'SAT I'
        and score = (select max(ax16.score)from ps_stdnt_test_comp
 ax16 where
        ax16.emplid = '100000303');
 
        When this statement it executed we get good results (1 row

 returned).  

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

        test_component = 'MATH' and test_id = 'SAT I'
        and score = (select max(ax16.score)from ps_stdnt_test_comp
 ax16 where
        ax16.emplid = '100000303');
 
        When this statement is executed we get 0 rows returned.  The
 only difference
        is changing the test_component from VERB to MATH.
 
        3.  When we recode the sql statement to this:
 
        select max(ax16.score) from ps_stdnt_test_comp ax16 where
 ax16.emplid =
        '100000303' and test_component = 'MATH'
 
         we get 1 row returned as we should.
 
        4. The recoding of the first SQL to
 
        select max(ax16.score) from ps_stdnt_test_comp ax16 where
 ax16.emplid =
        '100000303' and test_component = 'MATH'
 
         we get 1 row returned as we should.
 
 
        Any suggesting on how or who to contact to resolve a

 situation like this. Received on Tue Aug 29 2000 - 09:30:20 CDT