Archives of the TeradataForum

Message Posted: Mon, 10 Feb 2004 @ 00:36:27 GMT

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

Interesting observation! I just checked your ideas on Oracle and

select count(*) from demo.customer
where exists (select max(customer_id) from demo.customer where 1 < 0)

returns 33, which is indeed the full count, the same as in

select count(*) from demo.customer

I then modified the subquery (included the NVL function) to visualize what it returns.

select nvl(max(customer_id), -1) from demo.customer where 1 < 0

returns -1. Meaning that MAX returns NULL here. The fact that something is returned by the subquery makes Oracle EXISTS evaluate to TRUE. Everything is at least internally consistent.

The differences you described can be quite a gotcha with Oracle - TD migrations, so thanks for the warning. For what it's worth, I personally dislike EXISTS in all databases but sometimes it is unavoidable, so it's better to be prepared for those differences.

The query with UNION puzzles me the most, BTW.

Regards,

Victor

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