Data is getting rejected at lookup.
Moderators: chulett, rschirm, roy
Data is getting rejected at lookup.
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.
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.
ambasta
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
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)..
-
- Participant
- Posts: 222
- Joined: Tue Aug 30, 2005 2:07 am
- Location: pune
- Contact:
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.
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.
NageshSunkoji
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
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.
ambasta
-
- Participant
- Posts: 222
- Joined: Tue Aug 30, 2005 2:07 am
- Location: pune
- Contact:
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.
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.
NageshSunkoji
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
-
- Participant
- Posts: 222
- Joined: Tue Aug 30, 2005 2:07 am
- Location: pune
- Contact:
Yes u r understanding is proper,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!!!
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.
NageshSunkoji
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
If you know anything SHARE it.............
If you Don't know anything LEARN it...............