From oracle-l-bounce@freelists.org Wed May 5 08:46:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45Dk8K09942 for ; Wed, 5 May 2004 08:46:18 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i45Djw609906 for ; Wed, 5 May 2004 08:46:08 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 636D872CEE4; Wed, 5 May 2004 08:36:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26270-92; Wed, 5 May 2004 08:36:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AD78572CE72; Wed, 5 May 2004 08:36:45 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 08:35:33 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8042E72CE5B for ; Wed, 5 May 2004 08:35:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26099-98 for ; Wed, 5 May 2004 08:35:31 -0500 (EST) Received: from ihub.perceptron.com (ihub.perceptron.com [68.22.13.201]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1663772CE2A for ; Wed, 5 May 2004 08:35:31 -0500 (EST) Received: from [192.168.4.7] (ineyman@perceptron.com) by Office-Logic InterChange; Wed, 05 May 2004 09:51:03 -0400 From: "Igor Neyman" To: Subject: RE: Problem with joining char field to varchar2 field Date: Wed, 5 May 2004 09:49:29 -0400 Message-ID: <000301c432a7$c98ffac0$0704a8c0@development.perceptron.com> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Importance: Normal In-Reply-To: <4001DEAF7DF9BD498B58B45051FBEA656D8496@25exch1.vicorpower.vicr.com> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4430 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ineyman@perceptron.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Just checked 8.1.5 on NT and 10g on XP. In both cases default setting for "blank_trimming" is FALSE. But, it could be different in Warren's installation. Igor Neyman, OCP DBA ineyman@perceptron.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Goulet, Dick Sent: Wednesday, May 05, 2004 8:35 AM To: oracle-l@freelists.org Subject: RE: Problem with joining char field to varchar2 field Warren, There is a structural difference between a varchar and char datatypes. = Char's will get padded to the full size of the field whereas Varchar's = won't. To explain the differences in Windoze vs. AIX check the init = file to see if BLANK_TRIMMING is true. Something in the past tells me = that the default on Windoze is true and Unix is false. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -----Original Message----- From: Warren Homer [mailto:wazhomer@hotmail.com] Sent: Tuesday, May 04, 2004 8:51 PM To: oracle-l@freelists.org Subject: Problem with joining char field to varchar2 field=20 Hi all, we are currently running Oracle 9.2.0.1.0 on AIX 4.3. I am trying to join two tables on a varchar2(50) field and a CHAR(30) = field=20 where both are defined as NOT NULL. Although these two fields have the = same=20 value inserted Oracle interprets these two columns to be NOT equal. I = have=20 tried the same SQL test on a Window 2000 Oracle 9.2.0.3.0 environment=20 without any problems. Is this a bug in Oracle 9201 or a problem with AIX = ?=20 Thanks in advance for any help. The following shows the process I have used to create the problem. CREATE TABLE PTMBT01 (COL1_ID NUMBER(10) NOT NULL , COL2 CHAR(30) NOT NULL ) TABLESPACE PSMBT01_00 PCTFREE 5 PCTUSED 75; CREATE TABLE PTMBT02 (COL1_ID NUMBER(10) NOT NULL , COL2 VARCHAR2(50) NOT NULL ) TABLESPACE PSMBT02_00 PCTFREE 5 PCTUSED 75; INSERT INTO PTMBT01 VALUES(1,'111'); INSERT INTO PTMBT01 VALUES(2,'123'); INSERT INTO PTMBT02 VALUES(1,'123'); INSERT INTO PTMBT02 VALUES(2,'123'); the following SQL returns NO rows. However, it does return two rows (as=20 expected) when run in the Windows 2000 Oracle 9.2.0.3.0 environment. SELECT * FROM PTMBT01 T01 , PTMBT02 T02 WHERE T01.COL2 =3D T02.COL2; Thanks in advance, Warren. _________________________________________________________________ Mother's Day is May 9. Make it special with great ideas from the = Mother's=20 Day Guide! http://special.msn.com/network/04mothersday.armx ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------