use of coalesce in Tera data

Database

use of coalesce in Tera data

Hi,

i would like to know whether we could use coalesce in Teradata in the following way:-

coalesce('select top 1 dateprocessed from xyz',0)

my sql query is :-

select * from xyz where isnull(select top 1 dateprocessed from xyz,0) > '2010-08-22 20:12:21'

i wanted Teradata equivalent for this query

3 REPLIES
Enthusiast

Re: use of coalesce in Tera data

You can use a scalar subquery as an argument to coalesce, but "SELECT TOP n" is not allowed in a subquery.

Re: use of coalesce in Tera data

I do not see the logic in your provided query, but you can do something like:
SELECT *
FROM xyz AS a
CROSS JOIN (SELECT TOP 1 dateprocessed from xyz) AS b
WHERE ... ISNULL(b.dateprocessed,0 )
Senior Apprentice

Re: use of coalesce in Tera data

A TOP query without ORDER BY returns a random row (actually not random, but not the "last"/"first"/"highest")

Are you at the same site as this guy?
http://forums.teradata.com/forum/general/teradata-equivalent-query-of-sql-server

Dieter