changing datatype in view
Anthony Judish asked if an IDS 7.31+ view column could have a different data type from the table's, and then found CREATE VIEW x(y) AS SELECT b::CHAR(10) FROM a. Art Kagel said explicit casts work in 9.xx and 7.xx can coerce with a concatenation or UNION trick. He added that Informix converts types on fetch anyway.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
In IDS 7.31 and higher, is it possible to create a view where the datatype of the view column is different than the datatype of the underlying table? For example, table a has column b integer, but the view based on table a would have column b char(10).
I RTFM and found how to do this.
CREATE VIEW x(y) AS SELECT b::CHAR(10) FROM a
ANTHONY JUDISH said: > > I RTFM There's a first time for everything, I suppose! :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Yes, and why?
Yes: In 9.xx+ you can use an explicit type cast in the VIEW to modify the type
of a column returned. In 7.xx it may be possible to coerce the return value to
a different type:
create view coercion (mychar_version, ...) as
select myintcol || '', ...
from mytable ...;
-or-
create view coercion (mychar_version, ...) as
select ' ', ... from systables where tabid = 0
UNION ALL
select myintcol, ...
from mytable ...;
Why?: Informix will happily automatically convert any column to any compatible
host variable type. So, just fetch that int column into a char[10] type host
variable in C or 4GL and voila!
Art S. Kagel
----- Original Message -----
From: Anthony Judish <ids@iiug.org>
At: 1/ 4 15:04
In IDS 7.31 and higher, is it possible to create a view where the datatype of
the view column is different than the datatype of the underlying table? For
example, table a has column b integer, but the view based on table a would
have column b char(10).
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.