Stephen, To explicitly "declare" a data type for an expression in a view definition, you can use a trick selecting no rows from an unrelated table, then selecting the expressions you really want from the real table.
Say you want to define COL1 as a column of type DOUBLE. Look around your database for another DOUBLE column. If column frodo in table baggins is a type DOUBLE, you could then do this to force the column COL1 to have the datatype you desire: CREATE VIEW viewname (COL1) as + SELECT frodo FROM baggins WHERE count = 0 + UNION ALL + select (FLOAT(textcolname)) FROM tablename In a series of SELECT queries in a SELECT ... UNION SELECT ... series, R:Base uses the first SELECT to determine all datatypes that must match in the following queries. Bill On Thu, 04 Apr 2002 16:27:08 -0500, James (Jim) Bentley wrote: >Stephen, > >Strange as it may seem "COL1" may not be what you think it >is. If you defined the view: >CREATE VIEW viewname (COL1) as select (FLOAT(textcolname)) >FROM tablename > >Rbase may think the column type for COL1 is NOTE. When you >create views with columns that have an expression many times >rbase will record that datatype as NOTE. > >-- >Jim Bentley >American Celiac Society >[EMAIL PROTECTED] - email >(973) 325-8837 voice >(973) 669-8808 Fax > > >---- "Stephen Markson" <[EMAIL PROTECTED]> wrote: >> Before I report this to RDCC can someone please help to >> verify the >> following bug(?): >> >> A view column, Col1, is defined as FLOAT() of a TEXT table >> column. >> Pressing F3 and checking the columns in the view reveals >> that Col1 is of >> type DOUBLE, as it should be. >> >> However, when Col1 is used in an expression such as SELECT >> (Col1 - 0.0) >> FROM ViewName, error message #2151 appears: -ERROR- TEXT >> cannot be >> subtracted by DOUBLE. >> >> In other words, RBase still thinks col1 is a TEXT type, >> even though it >> lists as and displays floating point values. >> >> Try this: >> >> CREATE TEMPORARY TABLE temp (col1 TEXT 8) >> LOAD temp >> NONUM >> '123456' >> '234567' >> '345678' >> '123' >> '322' >> '52' >> '622' >> '67222' >> END >> CREATE TEMPORARY VIEW tempv (col1) AS SELECT (FLOAT (col1)) >> FROM temp >> SELECT col1 FROM tempv >> SELECT (col1-0.7) FROM tempv >> >> The first SELECT lists all the values with their decimal >> points. >> >> The second SELECT generates the error. >> >> Does anyone else get the error? >> >> NOTE: the problem does NOT occur with the INT function >> in the view. >> >> Workaround appears to be to not use FLOAT in view definitions. >> Use FLOAT >> in the subsequent expressions. For example, if you wanted >> to parse out a >> REAL value from a text column, the view definition would >> do the parsing, >> but the FLOAT should be done when it's needed: >> >> CREATE TEMPORARY VIEW tempv (col1) AS SELECT (SGET (col1,2,1)) >> FROM temp >> SELECT (FLOAT(col1)-0.7)) FROM tempv >> >> That works. >> >> Thanks for your assistance. >> >> >> Regards, >> >> Stephen Markson >> ForenSys The Forensic Systems Group >> www.ForensicSystemsGroup.com >> 416 482 2140 >> >> >> ================================================ >> TO SEE MESSAGE POSTING GUIDELINES: >> Send a plain text email to [EMAIL PROTECTED] >> In the message body, put just two words: INTRO rbase-l >> ================================================ >> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >> In the message body, put just two words: UNSUBSCRIBE rbase-l >> ================================================ >> TO SEARCH ARCHIVES: >> http://www.mail-archive.com/rbase-l%40sonetmail.com/ >> >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
