Page 1 of 2
replacing an empty value in the datastage
Posted: Tue Dec 19, 2006 10:20 am
by kirankota79
I have some NULL and empty values in column of file. I want to replace both of the values to some numeric value. I can replace a NULL with a value but i cannot for the empty one. Iam using If then else in datastage. Can anybody help me.
Posted: Tue Dec 19, 2006 10:25 am
by us1aslam1us
I am not sure but yu can do something like this:
Code: Select all
If IsNull(link.col) then '16161' elseif len(link.col)=0 then '2332' else link.col
Sam
Posted: Tue Dec 19, 2006 10:31 am
by kirankota79
i think this is not a valid sysntax..please give me the correct one
Posted: Tue Dec 19, 2006 10:37 am
by us1aslam1us
Play around with this:
Code: Select all
If (IsNull(link.col) OR len(link.col)=0) then 'XXX' else link.col
In previous post there should be space between else and if.
Sam
Posted: Tue Dec 19, 2006 10:38 am
by narasimha
You could have done a little changes and checked yourself.
Use
else if instead of
elseif
Code: Select all
If IsNull(link.col) then '16161' else if len(link.col)=0 then '2332' else link.col
Does that work?
Posted: Tue Dec 19, 2006 10:47 am
by kirankota79
In the file there is a character that looks like small square, whereever i have this character iam not able to replace with values. Other than that iam able to replace with the following statement.
If DSLink3.SSN = 'NULL' OR len(DSLink3.SSN)= '' then "123-12-0000" else DSLink3.SSN
Posted: Tue Dec 19, 2006 10:54 am
by narasimha
Not sure, it could be carriage returns, try replacing it with CHAR(13):CHAR(10) and see if it helps.
Posted: Tue Dec 19, 2006 12:48 pm
by kirankota79
it is not working. I can understand that i have some junk values in the column. I want to get rid of them and replace the junk values with some other value. does anybody has a clue?
Posted: Tue Dec 19, 2006 12:55 pm
by us1aslam1us
what is your OS and your source system? As suggested by narasimha, you might have the LF or CR or non-printable characters in the file and you need to get rid of them.
Sam
Posted: Tue Dec 19, 2006 12:57 pm
by kirankota79
my OS is window 2003 server.
Posted: Tue Dec 19, 2006 12:59 pm
by kirankota79
source system? i am using a sequential file as an input in which the data is from db2
Posted: Tue Dec 19, 2006 1:03 pm
by us1aslam1us
Posted: Tue Dec 19, 2006 1:10 pm
by kirankota79
sam.....as i have already mentioned the character is see here looks like a small square box. these is no information about these characters in your link.
i appreciate if you give me some clues regarding this.
Posted: Tue Dec 19, 2006 1:18 pm
by narasimha
What Sam is trying to tell you here is to try open the file and find out if you can decipher these "small squares".
Do you have cygwin or similar tools installed? If yes you can vi the file and easily find out if these characters are carriage returns or not.
I dont know a way to figure this out in windows.
May be some windows expert can comment on this.
Posted: Tue Dec 19, 2006 1:23 pm
by chulett
Dollars to Doughnuts that 'square' is a DOS End-Of-File marker, a Ctrl-Z. As long as there's only one that is, and it's at the end of the file.
Otherwise, you'll need to take a peek at it with a Hex Editor or viewer of some sort.