Query not returning same results when displaying one column less

From: Marc Eggenberger <marc.eggenberger_at_remove.itc.alstom.com>
Date: Thu, 17 Feb 2005 11:53:45 +0100
Message-ID: <MPG.1c7e96229c444b399896a9@iww.cacti.ch.alstom.com>

Hi there.

I have an Oracle Database 8.1.7.0.0 running on Windows 2000 SP4. I have a query from one customer which gives a different result when displaying one column less. No change to the where clause or the joins. Shouldnt then the same results be returned?

The query is like this:

SELECT a.toolid, c1.valnum AS YS
 FROM tms.TDM_TOOL a,

      tms.TDM_TOOLVALUES c1                 

WHERE TOOLCLASSID IN

('D01','D04','D06','D07','D08','M01','M02','M03','M04','M05','M06','M07'

,'M08','M09','M10','M12','T01','T02','T03','T04','T05','T06','T07','T08'
,'T09','T10')

  AND c1.toolid (+) = a.toolid
  AND c1.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	
                                              AND h.functypeid  = 

'YS'),0)
ORDER BY a.toolid

when I ommit the c1.valnum as sys then more entries are displayed. So the query look like this then:

SELECT a.toolid
 FROM tms.TDM_TOOL a,

      tms.TDM_TOOLVALUES c1                 

WHERE TOOLCLASSID IN

('D01','D04','D06','D07','D08','M01','M02','M03','M04','M05','M06','M07'

,'M08','M09','M10','M12','T01','T02','T03','T04','T05','T06','T07','T08'
,'T09','T10')

  AND c1.toolid (+) = a.toolid
  AND c1.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	
                                              AND h.functypeid  = 

'YS'),0)
ORDER BY a.toolid

Why is this? shouldn the same results beeing returned?

-- 
mfg
Marc Eggenberger

Received on Thu Feb 17 2005 - 04:53:45 CST