Page 1 of 1
Convert field with SQL type as BIT to Integer
Posted: Wed Jun 14, 2006 3:41 pm
by Krazykoolrohit
Hi,
Can anyone help me out in converting a column which has a SQL type as BIT to Integer in a transformer.
thanx,
Rohit
Posted: Wed Jun 14, 2006 3:51 pm
by Krazykoolrohit
I tries Int(FieldName) but it didnt work
Posted: Wed Jun 14, 2006 4:07 pm
by ray.wurlod
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.
Posted: Wed Jun 14, 2006 4:11 pm
by Krazykoolrohit
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
Posted: Wed Jun 14, 2006 4:12 pm
by ray.wurlod
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
as your derivation.
Posted: Wed Jun 14, 2006 4:13 pm
by Krazykoolrohit
yes. I have run the job again and then will give you the exact error message
Posted: Wed Jun 14, 2006 4:18 pm
by Krazykoolrohit
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.
Posted: Wed Jun 14, 2006 4:33 pm
by ray.wurlod
What's the source database?
Try changing the derivation of that column to
and changing its data type within DataStage also to INTEGER. If it's truly a bit type this is a legal cast.
Posted: Wed Jun 14, 2006 5:12 pm
by Krazykoolrohit
will do that. The source is Progress.
thanx for the info. will try and let you know
Posted: Thu Jun 15, 2006 7:47 am
by Krazykoolrohit
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
Posted: Thu Jun 15, 2006 8:14 am
by DSguru2B
What is your source and target database? thats what Ray had asked too.
And also provide the original source and target datatypes.
Posted: Thu Jun 15, 2006 8:22 am
by Krazykoolrohit
My source is progress and target is DB2
Posted: Thu Jun 15, 2006 8:56 am
by Krazykoolrohit
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