I remember this discussion, mostly... I remembered the alias, and I remembered to put the one select first. What I forgot is putting the other one in parenthesis!! And I have your big SQL class folder right here....
Karen -----Original Message----- From: Bill Downall <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, May 29, 2013 6:20 pm Subject: [RBASE-L] - Re: Creating a view 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
