handle_null()

[karthi_gana]

I have designed a simple job as below.

Seq File --> Modify --> Seq_File

Seq File :

eno int not null
ename varchar(25) null
sal decimal(16,8) null

my task is to change these fields from "Null" to "Not Null". So i just used modify stage with specification = handle_null(ename,'') & specification = handle_null(sal,0).

I have changed the column nullable property of those two columns as "Nullable = No". I ran the job and verified the output file. I don't see empty space & 0 in the NULL value location.

i have verified the manual and saw the handle_null(columnname,-128)

what is the meaning of -128 here? what i have to do to achieve my requirement?

[ray.wurlod]

-128 is the value with which you replace any null.

This can be represented as a single byte 10000000 (binary) which is also the DataStage internal representation of null.

You need a separate handle_null() specification for each column.

[karthi_gana]

if i give -128 what value will be applied for ename and sal columns. I mean

i have declared nullable = no for those two columns.

what about 10000000 ? what value will be applied if i use it?

i am not getting this.

handle_null is equivalent to isnull() function (sqlserver, sybase) ?

shall i give handle_null(columnname,'') ?

[zulfi123786]

I have changed the column nullable property of those two columns as "Nullable = No". I ran the job and verified the output file. I don't see empty space & 0 in the NULL value location.

From what you have said you are doing what has to be done, after you have used handle_null() tell us what you are finding in place of NULLs ?

[karthi_gana]

any more inputs?


my file is as below.


10,aaa,5000
20,,6000
30,ccc,
40,www,

i just saw only empty space. Actualli i gave like handle_null(sal,0). but i didn't see 0. for ename i gave like handle_null(ename,'null'). i didn't see 'null'.

10,aaa,5000
20,null,6000
30,ccc,0
40,www,0

[zulfi123786]

What values have you defined for "null field value" property of the sequential file stage which is at the reading end ?

[karthi_gana]

I didn't include this property. Thats why we are using "Modify" stage. right?

[zulfi123786]

No, if you haven't used the above said property there is no chance that the seq file stage would set NULLS while importing the data hence the handle_null() in the following stage has no effect.

Blanks and NULLS are completely different