Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-22-2011
01:55 AM

09-22-2011
01:55 AM

Weird error message for EXP(a really big negative number)

Hi,

when running EXP(1000) you get a complaint about an overflow, which is fine. But it really took me a good plenty of time to understand why I was getting a "Bad argument for ** operator" errors. My SQL contains both powers (**) and exponents EXP(). I tried to replicate the error with the power functions without success. Only much later I noticed than calling EXP(-100000) results in this error. I'm now damping the arguments also for too small numbers (I had it already for the positive case).

The point is that this error message is really distracting, especially as it refers to the totally unrelated (in the view of the user) ** power operator and the reference manuals talk only about cases like 0**0 for that error.

A better error message would be much more similar to the one for EXP(1000).

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-30-2017
02:03 AM

10-30-2017
02:03 AM

Re: Weird error message for EXP(a really big negative number)

Hi, I am getting the same error .

SELECT DatabaseName ,Tablename ,SUM(CurrentPerm) AS CurrentPerm ,MAX(CurrentPerm)*(HASHAMP()+1) AS PermWithSkew , 100-(AVG(CurrentPerm)/MAX(CurrentPerm)*100) AS Skewfactor ----, 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 AS SkewThresh , CASE WHEN MAX(CurrentPerm) / AVG(CurrentPerm+1) > 100 AND SUM(CurrentPerm) > 500000 THEN 'N' WHEN MAX(CurrentPerm) / MIN(CurrentPerm+1) > 1000 AND SUM(CurrentPerm) > 500000 THEN 'N' WHEN SUM(CurrentPerm) < HASHAMP()*128*1024 THEN 'Y' WHEN SUM(CurrentPerm) > 1024*1024*1024 AND Skewfactor > 15.0 THEN 'N' ----WHEN Skewfactor < 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 THEN 'Y' ELSE 'N' END AS SkewCompliant FROM DBC.TableSizeV GROUP BY 1,2 ORDER BY Skewfactor DESC;

Wanted to understand , what ind of bad data conversion is hapenning w.r.t EXP, and what we can change.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-30-2017
02:54 AM

10-30-2017
02:54 AM

Re: Weird error message for EXP(a really big negative number)

the query runs fine with commenting as shown below, how ever throws error, witout comment at two places..

How ever the same sql runs fine in other teradata systems.

SELECT

DatabaseName

,Tablename

,SUM(CurrentPerm) AS CurrentPerm

,MAX(CurrentPerm)*(HASHAMP()+1) AS PermWithSkew

, 100-(AVG(CurrentPerm)/MAX(CurrentPerm)*100) AS Skewfactor

, 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 AS SkewThresh

, CASE

WHEN MAX(CurrentPerm) / AVG(CurrentPerm+1) > 100 AND

SUM(CurrentPerm) > 500000 THEN 'N'

WHEN MAX(CurrentPerm) / MIN(CurrentPerm+1) > 1000 AND

SUM(CurrentPerm) > 500000 THEN 'N'

WHEN SUM(CurrentPerm) < HASHAMP()*128*1024 THEN 'Y'

WHEN SUM(CurrentPerm) > 1024*1024*1024 AND Skewfactor > 15.0 THEN 'N'

WHEN Skewfactor < 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 THEN 'Y'

ELSE 'N'

END AS SkewCompliant

FROM

DBC.TableSizeV

GROUP BY

1,2

ORDER BY

Skewfactor DESC;