| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Create view using CAST to change datatype
Hi,
First if you want some efective help you should have to give more
information
a table description and a view, will help (including error message).
My opinion is that you should not use '', and is and advice from other
people.
Even when it is accepted as null in 9i, there are other product where '' <>
NULL.
SQL> select * from dual where '' is null;
D
-
X
SQL> select * from dual where null is null;
D
-
X
SQL>
> 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
>
>
> -----Original Message-----
> From: Saira Somani-Mendelin [mailto:saira_somani_at_yahoo.com]
> Sent: Friday, March 19, 2004 9:53 AM
> To: oracle-l_at_freelists.org
> Subject: Create view using CAST to change datatype
>
>
> List,
>
> We have an issue where we would like to join two tables using a column
> of different data types, so we thought to create a view which could cast
> one of those join columns to match that of the other.
>
> The problem is that we are trying to cast a character column to a number
> column, which under normal circumstances would be fine, except that the
> default value for this column is ' ', we get an error when we try to
> query on it.
>
> We could of course just go the other way and cast the other table's
> column as character, but the other table can be joined with many other
> tables, and for BI reasons, we'd like to keep it that way.
>
> Any help is appreciated.
>
> Thanks,
> Saira
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 - 11:08:47 CST
 |
 |