Re: To force type CHAR in a view

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 07 Oct 2002 18:03:54 GMT
Message-ID: <3DA1CC6B.BB89B637@exesolutions.com>

Karsten Farell wrote:

> cf wrote:
> > (excuse me, my inglish is not very good)
> >
> > When i create the view V1 I will want to create a column NEW_COL1 CHAR(10)
> > and not VARCHAR2(10)
> >
> > Because I have a utility which at the time of the joint of the view V1 and a
> > table with a column in char(10),
> > detects the difference in type.
> >
> > SQL> create table t1 (col1 varchar2(8));
> >
> > SQL> desc t1
> > Name Type
> > ---------------------- -----------------
> > COL1 VARCHAR2(8)
> >
> > SQL> CREATE OR REPLACE VIEW V1 AS
> > select col1,
> > rpad(col1,10,' ') new_col1
> > from t1
> > /
> >
> > SQL> desc v1
> > Name Type
> > --------------------- -----------------
> > COL1 VARCHAR2(8)
> > NEW_COL1 VARCHAR2(10)
> >
> > I will want to create a column NEW_COL1 ==> CHAR(10)
> >
> > Thanks in Advance..
> > With My Best Regards...
> > Christophe
> >
> >

>

> I don't think you can do it (directly, at least), since the syntax of
> the view statement does not allow you to specify data types (such as CHAR).

>

> Your idea of using a function does not work because all the built-in
> character functions (except upper() and lower() sometimes) return a
> VARCHAR2: concat(), initcap(), lpad(), rpad(), ltrim(), rtrim(),
> replace(), soundex(), and translate(). Both upper() and lower() will
> return a CHAR if a CHAR is passed as an argument, but will return a
> VARCHAR2 otherwise.

>

> Of course, taking your function idea to the next step, you might be able
> to create a 'hack' that will work. Can you create a view on the other
> table (where you have a CHAR datatype)? Something like:

>

> create view v2 as
> select rtrim(char_column_name) varchar2_column_name
> from other_table;

>

> That will convert your CHAR column to a VARCHAR2 in the view and your
> join won't detect a difference in datatype. You would join table t1 with
> view v2 in your utility.

>

> Actually, I don't know if this little 'hack' will even work. You'll have
> to try it with your 'utility' program.

It can be done directly as I demonstrated.

CREATE VIEW vtest AS
SELECT dummy, ' ' X
FROM dual;

That having been done you just UNION ALL your actual query to the template created.

But I still think doing it makes no sense.

Daniel Morgan Received on Mon Oct 07 2002 - 13:03:54 CDT