Re: Redefining a column within a view

Thanks to Laurenz for providing the answer to my problem:

Try:

[...]

                 Substr(col3,1,1)::char(1) as col3_part1,

                 Substr(col3,2)::char(2)   as col3_part2,

[...]

Yours,

Laurenz Albe

We have a table which contains a 3 byte column with datatype CHAR(3) which we want to redefine within the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR function the resulting column datatype is TEXT. I would like to see the CHAR datatype, is there anything I can do to set the proper datatype. The sample view is below:

CREATE OR REPLACE VIEW schema.jeff_view AS

 SELECT col1 ,

                col2,

                Substr(col3,1,1) as col3_part1,

                Substr(col3,2)     as col3_part2,

                col4

  FROM schema.jeff_table;

Any suggestions would be appreciated.

Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering

ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

  FAX: (201) 748-1500  
jschade@iso.com