how to convert null as value?

[#1]

hi  by using nullasvalue i convert null values into numeric value  but by using if condition  in the script i want to convert in Title column 100 = MR like this  and  in first_name 100= m like this  and in last_name 100= jani  like this, gender 100 = male or female.

set nullvalue= '100';

NULLASVALUE *;

LOAD TITLE,

    "FIRST_NAME",

    "LAST_NAME",

    GENDER;

SQL SELECT *

FROM *****

Thanks

Madhu

[buzzy996]

try is way,

if(isnull(urfield),value,urfield)

wright the same way wherever u required.

[Peter_Cammaert]

Or alternatively, the preceding load might become something like this (takes into account empty fields which are also not very nice)

LOAD IF (len(trim(TITLE)) > 0, TITLE, 'MR') AS TITLE,

     IF (len(trim(FIRST_NAME)) > 0, FIRST_NAME, 'm') AS FIRST_NAME,

     IF (len(trim(LAST_NAME)) > 0, LAST_NAME, 'jani') AS LAST_NAME,

     IF (len(trim(GENDER)) > 0, GENDER, 'male or female') AS GENDER,

SELECT ...

Remove the NULLASVALUE statement.

Best,

Peter

[Anonymous]

not sure you want like this?

IF(TITLE='100', 'Mr,', TITLE) as TITLE,

IF([FIRST_NAME]='100','M', [FIRST_NAME]) as [FIRST_NAME],

IF([LAST_NAME]='100','Jani', [LAST_NAME]) as [LAST_NAME]