RE: sql giving unpredictable results

From: Carle, William T (Bill), NBSO <wcarle_at_att.com>
Date: Tue, 29 Aug 2000 08:33:59 -0500
Message-Id: <10603.115726@fatcity.com>

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

Received on Tue Aug 29 2000 - 08:33:59 CDT