Archives of the TeradataForum

Message Posted: Tue, 02 Sep 2003 @ 20:22:50 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, September 02, 2003 16:06 -->

What may be causing MINUS ALL to return more rows that NO EXISTS and NOT IN?

select svc_ordno from provgact
where svc_ordno is not null minus all
(select svc_ordno from svcoprovg);
RETURNS 1603447 rows

*I cannot select prov_act_id because the selects must have the same number of columns.

select svc_ordno, prov_act_id from provgact p
where svc_ordno is not null and
not exists
(select svc_ordno from svcoprovg s where p.svc_ordno=s.svc_ordno);
RETURNS 1569862 rows

select svc_ordno, prov_act_id from provgact
where svc_ordno is not null and
svc_ordno not in
(select svc_ordno from svcoprovg);
RETURNS 1569862 rows

provgact where svc_ordno is not null = 1737890 rows
svcoprovg = 134443 rows
1737890 - 134443 = 1603447
There I expect 1603447 rows to be returned.

Thanks.

	<Prev	Next>		<<First	<Prev	Next>	Last>>