Re: To force type CHAR in a view

Padderz give me the answer on an other forum.

"padderz" <member_at_dbforums.com> a écrit dans le message de news: 1902305.1034067329_at_dbforums.com...
>
> Well, it is true that querying string literals in a view creates a
> column of type CHAR, with length matching that of the literal, e.g.
>
> CREATE OR REPLACE VIEW view_name AS
> SELECT ' ' column_name
> FROM dual
> WHERE 1 = 0;
>
> ...results in column of CHAR (10). However, using UNION ALL to
> concatenate the results with those from another query does not (perhaps
> surprisingly) give rise to a CHAR (10) column in the result set, e.g.
>
> CREATE OR REPLACE VIEW view_name AS
> SELECT ' ' column_name
> FROM dual
> WHERE 1 = 0
> UNION ALL
> SELECT column_name
> FROM table_name;
>
> ...results in column of VARCHAR2 (10). Instead I would use the built-in
> function CAST to modify the datatype of the column, e.g.
>
> CREATE VIEW view_name AS
> SELECT CAST (column_name AS CHAR (10)) column_name
> FROM table_name;
>
> ...results in column of CHAR (10), as required.
>
> For info on CAST see:
>
> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/serve-
> r.920/a96540/functions15a.htm#1017549
>
> --
> Posted via http://dbforums.com
>
Received on Tue Oct 08 2002 - 14:24:37 CEST