Sounds like in the table the field c.marketcode is a char(3) instead of varchar2(3).
Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -----Original Message----- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L > I have a question for from one of my developers related to PL/SQL and how > data is loaded. > > I have a field (marketcode) that is defined as VARCHAR2(3). > > I have a problem when I try to load the value of '20' into this field. > All values with three characters work fine. The problem is when the value > is less then 3 characters. > > When tables A and B have data loaded into this field using SQL/Loader the > resulting value in the field appears to me as '20' with the third position > =null. > > I have a separate PL/SQL process that loads this field into table C. > When PL/SQL populates this same value into this field the field appears to > me as '20' with the third position = space. I can't use SQL/Loader for > this table as the data needs to be massaged before loading into Oracle. > Thus when you try to link the tables together it does not find a match. > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=C.marketcode; > > (this returns 0 records) > > If I change the SQl statement to the following: > > select A.marketcode, C.marketcode > from tableA A, tableC C > where A.marketcode=trim(C.marketcode); > > (it correctly matches these up) > > Things I have tried to remedy this problem: > 1) I have tried to modify my PL/SQL program to put a TRIM statement > around the marketcode field when I populate table C. This did not work. > 2) I have tried to check the 3rd position and if it is = space then I set > the third position to null. But the field in Oracle is still a space when > the program is finished. > > Does anyone have any thoughts on how I can properly output this field from > Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. > Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
