Bob, The critical detail to get right is the data types of the columns. You do this in the very first SELECT in a chain of SELECT .... UNION SELECT ... UNION SELECT statements. R:BASE will assume (or convert, with expressions) that the same data types occur in the same order in each of the successive SELECTs. If your calculation is DOUBLE, they be sure you select a DOUBLE column or expression at the correct position of the first one, and a NULL or (0) in the following SELECTs at that position. You must not put the NULL or the expression-of-indeterminate-datatype in the first SELECT.
(In the worst case, if you cannot beat the "data type mismatch" error, you may have to design a top SELECT statement that gets no rows at all, but will get the data types in the right order for the 8 columns. Then continue to the second SELECT that uses expressions.) Bill On Thu, Mar 11, 2010 at 9:15 AM, <[email protected]> wrote: > I am not sure this is possible, but I am sure the SQL gurus will know. > > > > Desired result is a view with the following key fields (other fields > included, but > > I can handle them) > > > > Three source tables, T1, T2, T3 > > > > Columns needed in view that I have questions about. > > > > T1.KeyColumn - This field common in all three tables > > > > (T2.ColumnX - T1.ColumnA) > > > > ((T2.ColumnX - T1.ColumnA) - T3.ColumnY) > > > > Now for the tricky part. Tables 1 and 2 will always have matching > > KeyColumn values, that would return all rows. I need a UNION > > for table 3 as it will NOT always contain data for each row in tables > > 1 and 2 but I need to see ALLl rows, whether table 3 has data or > > not. Can you do a computed column with a union select? > > > > Further to complicate matters The view will have 8 columns. > > 3 from table 1, 3 from table 2, 1 computed from T1 and T2 > > and the final computed from all three as shown above. > > > > So the union select for table 3 will only have two columns. > > The KeyColumn and the columnY used in the computation. > > It does not have any of the other 6 non-computed columns. > > > > I can do all the above with projecting temp tables and then > > table updates. However, this is in a real time app and my > > goal is to have CURRENT data showing in this view 100% of the > > time. I do not want to have to do continuous drop tables and > > project temp tables if it can be avoided. > > > > So, is the above view possible? > > > > Thanks, > > -Bob > > > > > > > > >
