Convert field with SQL type as BIT to Integer

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 3:41 pm

Hi,

Can anyone help me out in converting a column which has a SQL type as BIT to Integer in a transformer.

thanx,
Rohit

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 3:51 pm

I tries Int(FieldName) but it didnt work

ray.wurlod · Post by **ray.wurlod** » Wed Jun 14, 2006 4:07 pm

Try not doing anything at all to it.

What is the SQL data type in the imported table definition?

Remember that, within DataStage server jobs, there are no data types, so the cast may be performed implicitly and correctly for you.

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 4:11 pm

doing nothing does not help. It is resulting in job abort with the message that unable to convert to integer sine the target is not nullable it drops the record

ray.wurlod · Post by **ray.wurlod** » Wed Jun 14, 2006 4:12 pm

Are you sure this is a server job?

In server jobs (by default) NULL is 10000000 (which is possibly how a "bit" is stored) so it may be possible to use

Code: Select all

IsNull(InLink.Field)

as your derivation.

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 4:13 pm

yes. I have run the job again and then will give you the exact error message

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 4:18 pm

it says,

SQL3120W The field value in row "F0-1" and column "36" cannot be converted to an INTEGER value, but the target column is not nullable. The row was not loaded.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 14, 2006 4:33 pm

What's the source database?

Try changing the derivation of that column to

Code: Select all

CAST(FieldName AS INTEGER)

and changing its data type within DataStage also to INTEGER. If it's truly a bit type this is a legal cast.

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 5:12 pm

will do that. The source is Progress.
thanx for the info. will try and let you know

Krazykoolrohit · Post by **Krazykoolrohit** » Thu Jun 15, 2006 7:47 am

Using the cast function is throwing me an error:

SQLSTATE=37000, DBMS.CODE=-20003
[DataStage][SQL Client][ODBC][DataDirect][ODBC PROGRESS driver][PROGRESS]Syntax error (7587

I used CAST(fieldname as integer) and changed sql type to integer in the odbc stage

DSguru2B · Post by **DSguru2B** » Thu Jun 15, 2006 8:14 am

What is your source and target database? thats what Ray had asked too.
And also provide the original source and target datatypes.

Krazykoolrohit · Post by **Krazykoolrohit** » Thu Jun 15, 2006 8:22 am

My source is progress and target is DB2

Krazykoolrohit · Post by **Krazykoolrohit** » Thu Jun 15, 2006 8:56 am

Its working now. thanx a lot. There was a schema mismatch between the DB2 bulk load stage and the actual production table.

Ray's advice helped. thanx again