RE: VARCHAR in table, CHAR in view

Mark; I had the same initial question; "How do you change data types in a view?" The only thing I could come up with was to do something like the following: Create procedure var_to_char(char_col char(1024) Returning char; Return char_col; End procedure; {test this but you get the idea} CREATE VIEW my_view (vcol1) AS SELECT var_to_char(column1) FROM my_table GROUP BY column1;{At least this works in 7.3} So to answer Marty's question: There would be a slight performance hit while the engine makes the procedure call. The only way to make sure is to do some benchmarking. -----Original Message----- From: Mark Collins [SMTP:mcollins@us.dhl.com] Posted At: Wednesday, July 07, 1999 3:13 PM Posted To: Informix Conversation: VARCHAR in table, CHAR in view Subject: Re: VARCHAR in table, CHAR in view > So, I'm working on a system right now that has a requirement that the > character columns in our tables be of CHAR data type. This is due to an > interface with some legacy application and is not negotiable. > > My question is this- If I created a view against the table where the > only difference was that the table used VARCHAR and the view used CHAR, > would there be any kind of performance hit using the view? There > wouldn't be any filtering or joining in the view, just a one-to-one > mapping of tables to views with the data type conversion. > > Are there any internal issues I should know about before trying this? It's possible that they changed things without telling me, but last time I looked, you can't change data types with a view. The syntax is something like: CREATE VIEW my_view (vcol1, vcol2, vcol3, vcol4, vcol5) AS SELECT column1, column9, column3, column6, sum(column5) FROM my_table GROUP BY column1, column9, column3, column6; This allows you to change column names and assign names to aggregates, but does not allow specifying or changing column types. This assumes Informix 7.x. I don't know if 9.x still has this limit. Mark Collins mcollins@us.dhl.com Don't anthropomorphize computers. They don't like it.

Since I don't have IUS (or whatever they're calling it today) installed here, would anyone know if I can do this using a cast (::) in the view? Something like-- create view faketable as select varcharcol::char(30), othercol, etc from realtable; Or would Scott's method be better? Obviously his way would work with any version of the engine. If the cast thing would work, and especially if it worked well, the company would probably upgrade. Marty Allred In article <7m0aq2$8rh$1@news.xmission.com>, Scott Black <sblack@elsouth.com> wrote: > > Mark; > I had the same initial question; "How do you change data types in a > view?" > The only thing I could come up with was to do something like the > following: > > Create procedure var_to_char(char_col char(1024) > Returning char;> Return char_col; > End procedure; {test this but you get the idea} > > CREATE VIEW my_view > (vcol1) > AS SELECT var_to_char(column1) > FROM my_table > GROUP BY column1;> {At least this works in 7.3} > > So to answer Marty's question: > > There would be a slight performance hit while the engine makes the > procedure call. The only way to make sure is to do some benchmarking. > > -----Original Message----- > From: Mark Collins [SMTP:mcollins@us.dhl.com] > Posted At: Wednesday, July 07, 1999 3:13 PM > Posted To: Informix > Conversation: VARCHAR in table, CHAR in view > Subject: Re: VARCHAR in table, CHAR in view > > > So, I'm working on a system right now that has a requirement > that the > > character columns in our tables be of CHAR data type. This is > due to an > > interface with some legacy application and is not negotiable. > > > > My question is this- If I created a view against the table > where the > > only difference was that the table used VARCHAR and the view > used CHAR, > > would there be any kind of performance hit using the view? > There > > wouldn't be any filtering or joining in the view, just a > one-to-one > > mapping of tables to views with the data type conversion. > > > > Are there any internal issues I should know about before > trying this? > > It's possible that they changed things without telling me, but > last time I looked, > you can't change data types with a view. The syntax is > something like: > > CREATE VIEW my_view > (vcol1, vcol2, vcol3, vcol4, vcol5) > AS SELECT column1, column9, column3, column6, > sum(column5) > FROM my_table > GROUP BY column1, column9, column3, column6;> > This allows you to change column names and assign names to > aggregates, but does not > allow specifying or changing column types. This assumes > Informix 7.x. I don't > know if 9.x still has this limit. > > Mark Collins > mcollins@us.dhl.com > > Don't anthropomorphize computers. They don't like it. > -- --Marty Allred Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.