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" <saira_somani@xxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mercadante, Thomas F > Sent: March 19, 2004 9:54 AM > To: 'oracle-l@xxxxxxxxxxxxx' > 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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------