Concatination Problem for Tab delimited file

vivek · Post by **vivek** » Wed Jun 28, 2006 7:56 am

Hi All,
I have two columns comming from a Tab delimited file.One of them is Reference(Varchar) where the other is Rate( Float.)
My requirement is to concatenate them and put in to a Varchar field(Oracle).
example data: REFERENCE
prime
split
YRP
Nkl
rto

RATE

20.2
0.5
0.5

etc.......
i am able to concatenate them both, but it is not giving results if the rate column is empty.Also i have written a small routine to give @null as well empty('') exceptions.But still the the result is not there for where ever there is a null in the rate field.
i.e the end result is like this
prime 20.2
split 0.5
YRP 0.5
.........
.........
........
the dotted lines i represent means there is no data in the resultant column. is there any thing i am missing?
Version 7.5.1 server
Any help is appriciated.

kumar_s · Post by **kumar_s** » Wed Jun 28, 2006 8:06 am

Work on the other way, Check for null and give an empty character '' instead.

loveojha2 · Post by **loveojha2** » Wed Jun 28, 2006 8:11 am

Other options could be Len(col2)=0
or Col2=""
or isNull(Col2)
or Col2=@NULL.STR

kumar_s · Post by **kumar_s** » Wed Jun 28, 2006 8:16 am

loveojha2 wrote:Other options could be Len(col2)=0
or Col2=""
or isNull(Col2)
or Col2=@NULL.STR

Checked all the possible condition

But needless to check for empty string on a Float column.

vivek · Post by **vivek** » Wed Jun 28, 2006 8:25 am

Kumar and Loveojha
Thanks for your fast replies.
I have already tried "" but it wasn't working.
just now itried to make as IF len(column)=0
and it's just working fine.

If any one can through some light on how data stage recognizes a Null field that would be awesome.

Once again thanks alot.

pkomalla · Post by **pkomalla** » Wed Jun 28, 2006 8:28 am

I tried with the specification you gave, the results were fine. I got refernce column value when rate is null. results are
sap20.5
yrs
tmp10

I used -- refernce:rate as output column value in transformer

You can try with If len(rate)=0 then reference else reference:rate

vivek · Post by **vivek** » Wed Jun 28, 2006 8:35 am

But My question is can't we use the term"@null" and why?
Folks any idea on that??

DSguru2B · Post by **DSguru2B** » Wed Jun 28, 2006 9:33 am

Usually checking for null from a flat file is hard unless it has the control character ^@ which represents a null. Usually flat files dont have such un-printable characters or they are stripped implicitly. Thats why a null check wont work even though to a naked eye it does look like null. Thats where the len() functions really helps to see if the length is 0.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 28, 2006 4:01 pm

You can not sensibly perform comparisons with @NULL (the unknown value); the answer can only be "don't know".

I have an object in each of my hands. Please answer the following questions.
1. Are they the same?
2. Are they different?
3. Which one is larger?

See what I mean? Use the IsNull() function to answer the question "is this value null?".

htrisakti3 · Post by **htrisakti3** » Fri Jun 30, 2006 2:46 pm

I think you can use this function:
NullToEmpty(inlink.Premium) : NullToEmpty(inlink.RATE)