Convert Char 10 to Integer 10 gives unexpected results

[lpadrta]

My input is a SQL db column that is char 10 value 3600000008

I am trying to make the SQL db target integer 10 value 3600000008

I've created the correct results when sending output to a peek stage, using this transformation: AsInteger(input)

But when I target the SQL db, I'm getting a result of -694967268

I cannot change the structure of the target table.

I've been wrestling this for a while, and I could use some help. Seems like it should be a simple thing...

Lynda

[SURA]

Try to use CAST / CONVERT in query level

[lpadrta]

Thanks for the suggestion, SURA.

I found that I can use the tsql command to CAST the Char column to xml, bigint or sql_variant, none of which seems to be giving me what I need.

[SURA]

Even convert is not helping you?

[chulett]

Looks to me like yet another example of overflow, as in your value is too large to be represented in an Integer.

[SURA]

Good point.

[lpadrta]

When I tried to view the data using the ODBC stage, using CAST(input) as bigint, I can see the expected value.

When I tried to view the data using CAST(input) as int and with the column defined as int 10, then view the data using the ODBC stage, it warns Maximum integer value exceeded.

If I CAST(substring input,1,10) as int then view the data using the ODBC stage, it warns Maximum integer value exceeded.

If I CAST(substring input,1,9) as int then view the data using the ODBC stage, it shows the data less the 10th digit.

If I CAST(substring input,1,10) as bigint then view the data using the ODBC stage, it shows the data including the 10th digit.

So it seems like a 10 position vchar or char with a value like 1234567890 can't fit into an int10 column.

<corrected the line
If I CAST(substring input,1,9) as bigint
to read
If I CAST(substring input,1,10) as bigint>

[zulfi123786]

I am trying to make the SQL db target integer 10 value 3600000008

check what is the size of integer datatypes on your database and also check if the colum defined is signed or unsigned. even datastage integer cant hold the above value if the column is defined as signed (not unsigned)

[lpadrta]

The only thing I can see is that the target is integer, length 4, precision 10.

[zulfi123786]

in such case it should be a signed column and you dont have an option but to change the datatype to bigint or atleast change it to unsigned if you dont expect negative values

One cant park a truck in a garage designed for a car

[lpadrta]

For the time being, the rows that have data too large will have to be rejected until a fix is applied.

Thanks very much for your inputs.

[ray.wurlod]

3600000008 is too big to be a signed Integer. You'll need to make it a BigInt. The largest signed integer is 2147483647, the largest unsigned integer is 4294967295. So even uint32 can not cope with all 10-digit numbers.