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

11-17-2010
04:23 AM

11-17-2010
04:23 AM

Hello.

I have a table (a bigint, b bigint)? where for any a is true that a>=b.

When queriing:

select sqrt(a-b) from table;

there is error, bad argument in sqrt() (don't remember exactly).

select * from table where a**Returns 0 rows.**

So, why error appears? Is there any special features with data types?

Thanx.

I have a table (a bigint, b bigint)? where for any a is true that a>=b.

When queriing:

select sqrt(a-b) from table;

there is error, bad argument in sqrt() (don't remember exactly).

select * from table where a

So, why error appears? Is there any special features with data types?

Thanx.

12 REPLIES

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

11-17-2010
04:44 AM

11-17-2010
04:44 AM

Try to cast bigint to float

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

11-17-2010
04:55 AM

11-17-2010
04:55 AM

This is useless, first, when i meet the problem, fields was decimal(18,5) (this is enough for stored values).

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

11-17-2010
05:41 AM

11-17-2010
05:41 AM

Is this the error?

2604 Bad argument involving ... for SQRT function.

First try adding a condition to see if actually a >= b:

WHERE a >= b

Do you run that query in SQL Assistant pre-TD13?

Then it might be because of the ODBC SQRT function, too.

Uncheck Tools - options - query - 'Allow use of ODBC SQL extensions in queries'

I can't check this, because i don't have access to a SQLA 12 anymore.

Dieter

2604 Bad argument involving ... for SQRT function.

First try adding a condition to see if actually a >= b:

WHERE a >= b

Do you run that query in SQL Assistant pre-TD13?

Then it might be because of the ODBC SQRT function, too.

Uncheck Tools - options - query - 'Allow use of ODBC SQL extensions in queries'

I can't check this, because i don't have access to a SQLA 12 anymore.

Dieter

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

11-17-2010
05:56 AM

11-17-2010
05:56 AM

i'm use sqla v13.

query

"select * from table where a < b"

returns zero rows.

the error is "2603 Bad argument for SQRT function."

query

"select * from table where a < b"

returns zero rows.

the error is "2603 Bad argument for SQRT function."

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

11-17-2010
06:49 AM

11-17-2010
06:49 AM

Strange.

I would suggest to run that query from BTEQ or any other query tool, maybe it's because of a SQLA/ODBC/.NET "feature".

To eliminate issues with SQRT you could replace it with (a-b)**0.5.

How many rows are in that table?

Does it work for a single row?

select top 1 sqrt(a-b) from table;

If averythiong fails, could you post your actual query?

Before opening an incident :-)

Dieter

I would suggest to run that query from BTEQ or any other query tool, maybe it's because of a SQLA/ODBC/.NET "feature".

To eliminate issues with SQRT you could replace it with (a-b)**0.5.

How many rows are in that table?

Does it work for a single row?

select top 1 sqrt(a-b) from table;

If averythiong fails, could you post your actual query?

Before opening an incident :-)

Dieter

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

11-17-2010
07:29 AM

11-17-2010
07:29 AM

May be you mean (a-b) + 0.5?

I already use sqrt(abs(...)), but will try to run from bteq, thx.

>How many rows are in that table?

there are about 100 000 000 rows

>Does it work for a single row?

No, it doesn't.

Here you are (only field_names renamed):

select X,Y,

sqrt(cast(N * sum(CNT**2) as bigint) - cast(sum(CNT)**2 as bigint)) as D

from table_1

group by X,Y,N

I already use sqrt(abs(...)), but will try to run from bteq, thx.

>How many rows are in that table?

there are about 100 000 000 rows

>Does it work for a single row?

No, it doesn't.

Here you are (only field_names renamed):

select X,Y,

sqrt(cast(N * sum(CNT**2) as bigint) - cast(sum(CNT)**2 as bigint)) as D

from table_1

group by X,Y,N

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

11-17-2010
07:57 AM

11-17-2010
07:57 AM

It could be an overflow issue for the CNT column (the sum exceeds the column precision):

Here is an example:

CREATE MULTISET TABLE PRUEBA01(

X INTEGER NOT NULL,

Y INTEGER NOT NULL,

N INTEGER,

CNT BYTEINT)

PRIMARY INDEX(X)

;

*** Table has been created.

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO PRUEBA01 VALUES(1,1,1,111);

*** Insert completed. One row added.

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO PRUEBA01 VALUES(1,1,1,111);

*** Insert completed. One row added.

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT X,

Y,

SQRT(CAST(N * SUM(CNT**2) AS BIGINT) - CAST(SUM(CNT)**2 AS BIGINT)) AS D

FROM PRUEBA01

GROUP BY X,Y,N

;

*** Failure 2603 Bad argument for SQRT function.

SUM(CNT) exceeds the byteint precission and the SQRT returns the error.

Could you check that SUM(CNT**2) fits in the CNT datatype?

HTH.

Cheers.

Carlos.

Here is an example:

CREATE MULTISET TABLE PRUEBA01(

X INTEGER NOT NULL,

Y INTEGER NOT NULL,

N INTEGER,

CNT BYTEINT)

PRIMARY INDEX(X)

;

*** Table has been created.

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO PRUEBA01 VALUES(1,1,1,111);

*** Insert completed. One row added.

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO PRUEBA01 VALUES(1,1,1,111);

*** Insert completed. One row added.

*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT X,

Y,

SQRT(CAST(N * SUM(CNT**2) AS BIGINT) - CAST(SUM(CNT)**2 AS BIGINT)) AS D

FROM PRUEBA01

GROUP BY X,Y,N

;

*** Failure 2603 Bad argument for SQRT function.

SUM(CNT) exceeds the byteint precission and the SQRT returns the error.

Could you check that SUM(CNT**2) fits in the CNT datatype?

HTH.

Cheers.

Carlos.

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

11-17-2010
10:28 AM

11-17-2010
10:28 AM

Hi Carlos,

the query fails even if you use BIGINT instead of BYTEINT :-)

Remove the SQRT and see the negative value.

This looks like a part of a standard deviation calculation, in that case the formula is wrong:

Instead of "N * SUM" it's "CNT * SUM"

teradater should have shown the actual calculation in the beginning :-)

SUM(CNT**2) will not overflow, because the result of an exponentiation is a FLOAT, but SUM(CNT) might (as the cast to BIGINT).

If this happens better use DEC(38,0) instead of BIGINT.

To get the highest possible precision i would suggest:

sqrt(CNT * sum(cast(CNT**2 as DEC(38,0))) - sum(cast(CNT as DEC(38,0)))**2 ) as D

Dieter

the query fails even if you use BIGINT instead of BYTEINT :-)

Remove the SQRT and see the negative value.

This looks like a part of a standard deviation calculation, in that case the formula is wrong:

Instead of "N * SUM" it's "CNT * SUM"

teradater should have shown the actual calculation in the beginning :-)

SUM(CNT**2) will not overflow, because the result of an exponentiation is a FLOAT, but SUM(CNT) might (as the cast to BIGINT).

If this happens better use DEC(38,0) instead of BIGINT.

To get the highest possible precision i would suggest:

sqrt(CNT * sum(cast(CNT**2 as DEC(38,0))) - sum(cast(CNT as DEC(38,0)))**2 ) as D

Dieter

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

11-17-2010
11:46 PM

11-17-2010
11:46 PM

CarlosAL, when i use sqrt(abs(...)), query isn't fails.

dnoeth, the formula is right, it's unsuccessful renaming variables.

Isn't DECIMAL(38,0) and float are the same?

Thanx, i'll try the first one, and write after this here

dnoeth, the formula is right, it's unsuccessful renaming variables.

Isn't DECIMAL(38,0) and float are the same?

Thanx, i'll try the first one, and write after this here