Teradata equivalent Query of SQL Server

General

Teradata equivalent Query of SQL Server

Hi All,

I want the teradata equivalent query for the below SQL Server Query.

SELECT *
FROM CAP_SAC_CP211
WHERE
ISNULL ((select top 1 cp2_eff_dt from CAP_SAC_CP211),0) >
ISNULL ((SELECT MAX(EFF_DT) FROM CORPT_PLCY_CR_WKLY_SNAP_DATA),0)

Thanks in advance.

Jojo George
Tags (1)
7 REPLIES
Teradata Employee

Re: Teradata equivalent Query of SQL Server

Hi,

You can use the same query .... and replace ISNULL with COALESCE.

Regards, MAC
Enthusiast

Re: Teradata equivalent Query of SQL Server

While it is true that COALESCE is equivalent to ISNULL, writing a completely equivalent query is not quite that easy. Teradata does not allow "select top n" in a subquery.

Even conceptually, "select top 1" in a subquery is nonsensical in Teradata since it presumes some ordering of the result set. If you can explain what result you expect from that subquery, it might be possible to suggest an equivalent query.
Junior Contributor

Re: Teradata equivalent Query of SQL Server

Strange query:
- if cp2_eff_dt/EFF_DT is actually a datetime in SQL Server, then why it's coalesced with an integer zero?

- it's all rows from CAP_SAC_CP211 or none, but it's kind of random, because TOP without order by is non-deterministic in SQL Server, too. If this actually returned the maximum date, then it was the right clustered index and luck.

Without TOP this is similar:
SELECT *
FROM CAP_SAC_CP211
WHERE EXISTS
(select * from CAP_SAC_CP211 where cp2_eff_dt >
coalesce (SELECT MAX(EFF_DT) FROM CORPT_PLCY_CR_WKLY_SNAP_DATA), date '0001-01-01')
)

Dieter
Fan

Re: Teradata equivalent Query of SQL Server

Hi.....we provide you seo process,its really very helpful for those who want to join seo process,visit the following link SEO Sevices,absolutely you'll get lot's of information.
Enthusiast

Re: Teradata equivalent Query of SQL Server

can it be taken then that --> COALESCE in teradata is equivalent to ISNULL in SQL ? also is there any function "ISNULL()" in teradata ?

Teradata Employee

Re: Teradata equivalent Query of SQL Server

COALESCE is the SQL standard way to accomplish the job. Teradata has ZEROIFNULL and NVL as well. 

Enthusiast

Re: Teradata equivalent Query of SQL Server

Thanks Todd.