All Forums Database
20 Jan 2016
Casting a DEC(10,4) to INT - strange results

Hello!
Consider these four queries:
SELECT *
FROM dbase.qtable
WHERE CAST(c_Order_ID AS INT) = 169
;
SELECT *
FROM dbase.qtable
WHERE TRUNC(c_Order_ID) = 169
;
SELECT *
FROM dbase.qtable
WHERE CAST(CAST(c_Order_ID AS INT) AS VARCHAR(15)) = 169
;
SELECT *
FROM dbase.qtable
WHERE CAST(TRIM(c_Order_ID) AS INT) = 169
;
The c_Order_ID field in the table is a DEC(10,4). Order ID has the order number to the left of the decimal and the lines making up the order to the right. In this instance, we have 10 rows for Order IDs 169.0000 through 169.0009.
The first query returns only one row, the row for Order ID 169.0000. The other three queries returns all 10 rows.
This is on Teradata v15.01 using Teradata.Net connectivity in SQLA. I also tested ODBC with the same results. I am just curious as to why CASTing to INT doesn't behave the same way as the other methods.

dnoeth 4628 posts Joined 11/04
21 Jan 2016

Looks like an optimizer bug to me, Explain shows a single-AMP PI-access onĀ 15.10.00.08, too.
Open an incident.

Dieter

21 Jan 2016

Hi Dieter!
Thanks for the reply. I thought this may be a bug. I'll open an incident.
Andrew

You must sign in to leave a comment.