When you create a view with a UNION in it, R:Base sets the datatypes for the columns in the views based on the first SELECT.
The parentheses around the shorter column that you want to line up with the 40-character column turn that column into an R:BASE "Expression", and R:BASE figures out what datatype to turn it into, in this case the datatype of the matching column from the earlier SELECT. I think we went over this in a DMB & Assoc training class about 18 years ago, Karen. You already have it in your notes somewhere! :-) On Wed, May 29, 2013 at 6:56 PM, Karen Tellef <[email protected]> wrote: > I bow down to you... Now can you please explain why that works? > I assume the important parts are: > 1. Create a temporary column alias > 2. You put the select with the bigger (?) text width first > 3. And for the other select enclose the column in parenthesis > > I need to put this in my notes for future reference but I'd love to know > why this works... > > Again, thanks! > > Karen > > > > -----Original Message----- > From: Bill Downall <[email protected]> > To: RBASE-L Mailing List <[email protected]> > Sent: Wed, May 29, 2013 5:47 pm > Subject: [RBASE-L] - Re: Creating a view > > Karen, > > Put the BillingAddress part of the query first, and in the second part > of the query, put Address in parentheses. > > CREATE VIEW vCompany (CompanyID, tmpAddress) + > AS SELECT companyid, BillingAddress FROM company WHERE type = 'B' + > UNION ALL SELECT + > companyid, (address) FROM company WHERE type = 'A' > > > > On Wed, May 29, 2013 at 6:12 PM, Karen Tellef <[email protected]> wrote: > >> This is driving me crazy... Trying to create a view, here's the pared >> down example: >> >> CREATE VIEW vCompany (CompanyID, Address) + >> AS SELECT companyid, address FROM company WHERE type = 'A' + >> UNION ALL SELECT + >> companyid, billingaddress FROM company WHERE type = 'B' >> >> I get a "column type mismatch in union". The problem is that the Address >> and the >> BillingAddress are different lengths. Address is Text 25 and >> BillingAddress is Text 40. >> It refuses to put the 2 together. >> >> I've tried these, and still get the mismatch: >> 1. Leaving out the alias column names and letting the view definition >> decide. >> 2. Creating a new column alias name like: >> >> CREATE VIEW vCompany (CompanyID, tmpAddress) + >> AS SELECT companyid, address FROM company WHERE type = 'A' + >> UNION ALL SELECT + >> companyid, billingaddress FROM company WHERE type = 'B' >> >> All of it gives me a union type mismatch. I cannot have different >> lengths of >> TEXT columns? >> >> Anyone think of a workaround? I'm going to be using this in a report, >> and I suppose >> I could add code to create a temp table on the fly, but I really wanted a >> view... >> >> Karen >> >> >> > >
