Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Query not returning same results when displaying one column less

Query not returning same results when displaying one column less

From: Marc Eggenberger <marc.eggenberger_at_remove.itc.alstom.com>
Date: Tue, 22 Feb 2005 07:23:51 +0100
Message-ID: <MPG.1c84ee5e10be620f9896ab@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 Tue Feb 22 2005 - 00:23:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US