All Forums General
jojogeorge 1 post Joined 09/10
03 Sep 2010
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

Adeel Chaudhry 773 posts Joined 04/08
03 Sep 2010

Hi,

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

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

Jim Chapman 449 posts Joined 09/04
03 Sep 2010

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.

dnoeth 4628 posts Joined 11/04
04 Sep 2010

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

Dieter

cruz55 2 posts Joined 09/10
15 Sep 2010

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.

SHERIN JEYABOSE 18 posts Joined 11/14
12 Dec 2014

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

ToddAWalter 316 posts Joined 10/11
12 Dec 2014

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

SHERIN JEYABOSE 18 posts Joined 11/14
14 Dec 2014

Thanks Todd.

You must sign in to leave a comment.