Jan, Yes. You can create a view in the form:
CREATE VIEW myView + (columnlist) + AS + SELECT columnlist1 + FROM tablename + WHERE whatever + UNION ALL + SELECT columnlist2 + FROM tablelist2 + WHERE whatever In your case, the first SELECT will have a WHERE clause that finds NO rows, but chooses explicit columns of the datatypes you want in your view. (R:BASE will decide on the datatypes of the columns in the view based on the first SELECT in a series of SELECTS built with the UNION connector.) Then, in your successive SELECTs, be sure to surround any column or expression that does not already precisely match the datatype from the first SELECT in parentheses. Here's an example with CONCOMP that gives you a NOTE type where you don't want one: CREATE VIEW ContactNameView ( CustID, CNameL_comma_F, contphone ) AS + SELECT custid, (contlname + ',' & contfname) contphone + FROM contact Here's another way to do the view where the column CNameL_comma_F ends up with a TEXT (40) datatype. It is important to note that ContPhone and CustPhone both have the same datatype, TEXT(12). You must have the same number of items in each SELECT, and the datatypes must match precisely left to right. Be sure the first SELECT finds no rows, and has no expressions that R:BASE will interpret into datatypes implicitly. Expressions in second or later SELECTS will be cast into the datatype of the first select, if R:BASE can reasonably do that. DROP VIEW ContactNameView CREATE VIEW ContactNameView (CustID, CNAmeL_comma_F, contphone _ AS + SELECT CustID, Company, CustPhone + FROM Customer + WHERE CustID = 0 + UNION ALL + SELECT custid, (contlname + ',' & contfname) contphone + FROM contact Bill Jan Johansen wrote: > Group, > > In order to transfer data out of the old database to new I have needed to > create some monster views. > However, when I use functions such as IFEQ or IFNULL the resulting column in > the view becomes a NOTE. > Usually this does not seem to be a problem. > > BUT is there a way to define the column type in a view. I know normally it > picks it up from the table. > > Jan > > >
