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
