From oracle-l-bounce@freelists.org Fri Mar 19 11:10:14 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JHAEO10627 for ; Fri, 19 Mar 2004 11:10:14 -0600 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 i2JHADo10621 for ; Fri, 19 Mar 2004 11:10:13 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EC5BC390E31; Fri, 19 Mar 2004 12:08:17 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 12:07:11 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mail.acelerate.com (mail.acelerate.com [200.105.128.132]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 4F076390DBF for ; Fri, 19 Mar 2004 12:06:45 -0500 (EST) Received: (qmail 993 invoked from network); 19 Mar 2004 15:02:33 -0000 Received: from dazasoftware.com (HELO org48l7d9ara8b) (200.105.151.94) by 0 with SMTP; 19 Mar 2004 15:02:33 -0000 Message-ID: <002901c40dc4$0c5a92b0$2501a8c0@dazasoftware.com> From: "Juan Cachito Reyes Pacheco" To: References: <004301c40dc3$53d5c980$e20110ac@itlap01> Subject: Re: Create view using CAST to change datatype Date: Fri, 19 Mar 2004 11:08:19 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-archive-position: 1265 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jreyes@dazasoftware.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l Aditionally i verified definetively in 9i, '' is the same that null, WHEN INSERTING TO THE TABLE, NOT when doing a query. SQL> create table test1 ( label varchar2(1), thenull varchar(1)); Tabla creada. SQL> insert into test values('a',''); insert into test values('a','') * ERROR en lƯnea 1: ORA-00942: la tabla o vista no existe SQL> insert into test1 values('a',''); 1 fila creada. SQL> insert into test1 values('b',null); 1 fila creada. SQL> select * from test1 where thenull is null; <-- using null you get both L T - - a b SQL> select * from test1 where thenull =''; <--- using '' you get nothing ninguna fila seleccionada SQL> ----- Original Message ----- From: "Saira Somani-Mendelin" To: Sent: Friday, March 19, 2004 11:03 AM Subject: RE: Create view using CAST to change datatype > Tom, > > That's a great suggestion, but how could I do that in a view? Perhaps > like this? CAST(RTRIM(OB_OID) AS NUMBER) OB_OID... would that work? > > Here is the view definition: > > CREATE OR REPLACE VIEW V_ITH_F > AS > (SELECT > ITH_RID, > TRANSACT, > SKU, > PKG, > FROM_LOC, > TO_LOC, > TRANSACT_STT, > WAVE, > CAST(OB_OID AS NUMBER) OB_OID, > OB_TYPE, > CAST(IB_OID AS NUMBER) IB_OID, > IB_TYPE, > OB_LNO, > IB_LNO, > ACT_QTY, > WHSE, > FROM > ITH_F); > > -----Original Message----- > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org] On Behalf Of Mercadante, Thomas F > Sent: March 19, 2004 9:54 AM > To: 'oracle-l@freelists.org' > Subject: RE: Create view using CAST to change datatype > > Saira, > > My first idea would be to trim the blanks from the column thus > converting > the value to a null before casting. Would that work? What happens with > the > Cast statement when a null value is involved? Can you show the Create > View > Sql? > > thanks > > Tom Mercadante > Oracle Certified Professional > > ---------------------------------------------------------------- > 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 -----------------------------------------------------------------