Query not returning same results when displaying one column less

Marc Eggenberger · *nix forums beginner Joined: 21 Apr 2005 Posts: 6

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

Google