Data is getting rejected at lookup.

ambasta · Post by **ambasta** » Wed Oct 11, 2006 7:57 am

Hi,
I am performing a job from file to Repository,I am doing lookup from one table as the target is having referencital integrity on it.Suppose I am getting data (1 --one followed by three space)from source file..i m trimming and sending it to the lookup stage where this data is getting rejected and reason it is giving that this data doesnot exist in lookup table,but required data(i.e. 1) is there in lookup table.when I am loding the meta data in extended column it is showing unicode. Actual length is (varchar[4]) but when I m loding metadata it is coming as (varchar[16]). is the problem due to unicode character????Is there any way out???please help me.

Thanks in Adavnce

kumar_s · Post by **kumar_s** » Wed Oct 11, 2006 8:09 am

Search the forum, you can find a post in the same regards.
If you were not able to convert the lookup data, convert the input stream according to the lookup, you have ustring_from_string functon avaialble in transformer.
If its Char, I usually prefer to convert it into Varchar. You can edit the data length as well using transformer.

kris007 · Post by **kris007** » Wed Oct 11, 2006 9:52 am

When ever I import metadata where datatypes are CHAR, I uncheck the unidata option and that works for me. It might work for you as well. Also, when you are trimming the data and sending it to Lookup stage, what is the datatype of that column in the Lookup stage?

ambasta · Post by **ambasta** » Wed Oct 11, 2006 10:20 am

kris007 wrote:When ever I import metadata where datatypes are CHAR, I uncheck the unidata option and that works for me. It might work for you as well. Also, when you are trimming the data and sending it to Lookup stage, what is the datatype of that column in the Lookup stage?

From Source Char(4) is Coming and i m doing lookup based on column having datatype varchar(16)[unicode]

Nageshsunkoji · Post by **Nageshsunkoji** » Wed Oct 11, 2006 10:25 am

Kunal,

If you are performing lookup CHAR to VARCHAR, you will not get exact result. So, first convert u r source from Char to Varchar. We faced lot of issues, while performing lookup CHAR with VARCHAR. Solution is make the both fields to Varchar and perform lookup. I think u will get the result.

kris007 · Post by **kris007** » Wed Oct 11, 2006 10:47 am

ambasta wrote: i m trimming and sending it to the lookup stage where this data is getting rejected and reason it is giving that this data doesnot exist in lookup table

If you are trimming your source data and then again sending it as a CHAR to LookUp Stage then it wont help because the spaces are added back again to your data. As suggested, you will need to change it to Varchar while trimming it and then you will be OK.

ambasta wrote:
From Source Char(4) is Coming and i m doing lookup based on column having datatype varchar(16)[unicode]

You will need to convert your source datatype to Varchar, while trimming it and then do the lookup.