Data is getting rejected at lookup.

ambasta · Post by **ambasta** » Tue Oct 24, 2006 1:26 pm

Hi All,
In My job i am using one lookup table based one column having length Varchar(12),but the actual data is coming of length 8.I am trimming that field at oracle and then doing the lookup.When i am making length as 12 all data are getting rejected at lookup,but if i am giving length as 8 data is passing lookup.I know this is not the right way because in future i can get data of length upto 12..can any one please help me on this.

Thanks in Adavnce.

talk2shaanc · Post by **talk2shaanc** » Tue Oct 24, 2006 1:33 pm

If I were you, I would have made the columns of same length, by doing formatting (like trimming, left justify space filled) for both LOOK-UP and MAIN Stream. Before bringing it to look-up stage.

ambasta · Post by **ambasta** » Tue Oct 24, 2006 1:39 pm

Thanks a lot Shantanu for your quick and kind suggession.
I am already doing the Trim of Lookup column as well as main Stream.Is this any Unicode data problem??

talk2shaanc · Post by **talk2shaanc** » Tue Oct 24, 2006 6:50 pm

u didnt get my msg..assuming field colA has length 5 on the table. Now while loading the table, there must be some formatting standard..(e.g., for char fields ..trim leading and trailing spaces, then left justify the column and fill the remaining bytes with spaces "abc ")...now u have a record coming in with length 3, i wd do similar formatting to make both the value same in terms of formatting, before doing a look-up (trim leading & trailing spaces, left justify space fill)..

Nageshsunkoji · Post by **Nageshsunkoji** » Wed Oct 25, 2006 4:21 am

Hi Kunal,

Just remember, whenever your doing lookup the filed datatype should be same in both Main stream and reference stream. here Length is a problem , when ever your performing lookup on CHAR to CHAR field. Length is not at all a problem when you are performing lookup on Varchar to Varchar field. Just check if both fields of your Main stream and reference stream same or not ? If both are not same then convert the char datatype to varchar. If both have the datatype varchar, then I am sure definetely u will get the result without any hesitation.

Regards,
Nagesh.

ambasta · Post by **ambasta** » Wed Oct 25, 2006 9:32 pm

Nageshsunkoji wrote:Hi Kunal,

Just remember, whenever your doing lookup the filed datatype should be same in both Main stream and reference stream. here Length is a problem , when ever your performing lookup on CHAR to CHAR field. Length is not at all a problem when you are performing lookup on Varchar to Varchar field. Just check if both fields of your Main stream and reference stream same or not ? If both are not same then convert the char datatype to varchar. If both have the datatype varchar, then I am sure definetely u will get the result without any hesitation.

Regards,
Nagesh.

Hi Nagesh,
Thanks for your Quick Reply.but you can't always be so sure that if[both have the datatype varchar]then always it will work.I Faced the same Scenario.I had both datatype as varchar,even though it was not working.for that column not even trim or wtripwhitesapce was working.then i took the substirng of that cloumn and then trimmed leading trailing.now it is working fine.

Thanks.

Nageshsunkoji · Post by **Nageshsunkoji** » Thu Oct 26, 2006 3:12 am

Hi Kunal,

Thats nice, you got the result. Regarding Varchar, see if your input data is coming from a extract from database then in the database itself make it output field datatype as Varchar and do the same thing for reference data also. then I am sure u will get the result. But, I observed one more thing, your trim function is not working and Substring is working. That means those are not spaces may be that is representing ASCII nulls. check the APT_STRING_PAD_CHAR in the Project level it should be 0x20.

ambasta · Post by **ambasta** » Thu Oct 26, 2006 3:28 am

Thanks a lot Nagesh,
I am not aware of this Environment Variable called APT_STRING_PAD_CHAR.can you please explain what is the functionality of this and what it does.

Thanks in Advance.

ambasta · Post by **ambasta** » Thu Oct 26, 2006 4:47 am

Thanks a lot for this great Suggession.I will take care of this in Future.
As I Understood if i will set APT_STRING_PADCHAR as 0x20 and then trim the column RECORD as Trim(RECORD) then we will get [Ready ] as [Ready].Right!!!

Nageshsunkoji · Post by **Nageshsunkoji** » Thu Oct 26, 2006 4:58 am

ambasta wrote:Thanks a lot for this great Suggession.I will take care of this in Future.
As I Understood if i will set APT_STRING_PADCHAR as 0x20 and then trim the column RECORD as Trim(RECORD) then we will get [Ready ] as [Ready].Right!!!

Yes u r understanding is proper,
But, always remember while using APT_STRING_PADCHAR, you have to change the datatype of CHAR fileds to VARCHAR to get the proper results.

Regards,
Nagesh.