replacing an empty value in the datastage

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 10:20 am

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.

us1aslam1us · Post by **us1aslam1us** » Tue Dec 19, 2006 10:25 am

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

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 10:31 am

i think this is not a valid sysntax..please give me the correct one

us1aslam1us · Post by **us1aslam1us** » Tue Dec 19, 2006 10:37 am

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

narasimha · Post by **narasimha** » Tue Dec 19, 2006 10:38 am

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?

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 10:47 am

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

narasimha · Post by **narasimha** » Tue Dec 19, 2006 10:54 am

Not sure, it could be carriage returns, try replacing it with CHAR(13):CHAR(10) and see if it helps.

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 12:48 pm

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?

us1aslam1us · Post by **us1aslam1us** » Tue Dec 19, 2006 12:55 pm

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

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 12:57 pm

my OS is window 2003 server.

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 12:59 pm

source system? i am using a sequential file as an input in which the data is from db2

us1aslam1us · Post by **us1aslam1us** » Tue Dec 19, 2006 1:03 pm

This might help you:

http://www.vim.org/tips/tip.php?tip_id=379

Sam

kirankota79 · Post by **kirankota79** » Tue Dec 19, 2006 1:10 pm

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.

narasimha · Post by **narasimha** » Tue Dec 19, 2006 1:18 pm

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.

chulett · Post by **chulett** » Tue Dec 19, 2006 1:23 pm

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.