RE: Create view using CAST to change datatype

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 19 Mar 2004 10:09:54 -0500
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5A0C@exchsen0a1ma>

Saira,

This worked:

CREATE OR REPLACE VIEW tomview
AS
SELECT
col1, col2,
cast(NVL(RTRIM(col1),0) AS NUMBER) col1_numb FROM tomtest

Values for the TOMTEST table were (' ',1);

SELECT * FROM tomview
returns

values of (' ', 1, 0)

And I agree wih Igor - why are you using CAST rather than to_number?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: Saira Somani-Mendelin [mailto:saira_somani_at_yahoo.com] Sent: Friday, March 19, 2004 10:03 AM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F Sent: March 19, 2004 9:54 AM
To: 'oracle-l_at_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_at_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_at_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

Received on Fri Mar 19 2004 - 09:06:34 CST