Steve, There is a way to force exactly the data type you want. In a UNION SELECT, R:BASE sets the datatype of each view column based on the *first* in a series of SELECT statements, and forces later ambiguous datatypes to match.
If you want the column to be text(4) in your view, find any column in your database that has a text(4) datatype. Begin your view definition by finding *no rows* in a SELECT statement that has exactly the right number of columns, with datatypes of columns in the exactly right order left to right. UNION ALL the no rows select with your real view. e.g, if your original View was: CREATE VIEW myView + (col1, col2, col3, col4, top_column) + as select + col1, col2, col3, col4, (call sp(this, that, theother)) + from sometableOrList And you have a column junk Text(4) in the table whatsit, then define your view as: CREATE VIEW myView + (col1, col2, col3, col4, top_column) + as select + col1, col2, col3, col4, junk + from someTableOrList s1, whatsit w2 + where (s1.pkeycol = -1) AND w2.primarykeycol = -1) UNION ALL SELECT + col1, col2, col3, col4, (call sp(this, that, theother)) + from sometableOrList By putting the expression into a later SELECT, you convince R:BASE to try and see if the datatype of the call to your sp could possibly be cast into the datatype of "junk" in the first select. If it couldn't, you would get the dreaded "column type mismatch" error. Bill On Fri, Mar 14, 2008 at 6:32 PM, James Bentley <[EMAIL PROTECTED]> wrote: > Steve, > > One of the facts of life about VIEW definition I learned long > ago is that when a column in the view select statement is really > a computation RBase ALWAYS assigns that column the NOTE data > type. This was a hard learned lesson as I tried to debug a > vexing problem. > > Jim Bentley > > --- "Wills, Steve" <[EMAIL PROTECTED]> wrote: > > > I figured this one out. My TOP_COLUMN was typed as NOTE. I > > hadn't even > > considered that. It's value is a Fiscal CCYY, returned as > > TEXT via a > > CALL to a Stored Procedure. However, when I use this in a > > view, then > > save the view to create a 'base' table, it appears to be > > implicitly > > typed as NOTE. (I'm pretty sure that my original PUT of the > > Stored > > Procedure explicitly typed the Return Value - thanks to > > whomever on the > > list pointed that out to me a week or so back.) > > > > Anyway, I'd like to ask Razzak and RBTI if would be possible > > for > > CROSSTAB to check for invalid data types, then, if found, > > throw an error > > message, rather than throw out the program. > > > > This ain't no complaint, just polite request. > > > > Admittedly, the root-node cause (how do you like that > > phrasing, Jim > > [Bentley]) is mine, but I think it would be far more elegant > > to tell me > > I screwed up rather than just punish me without my knowing > > why. > > > > My Ever-Decreasing-In-Value $0.02 (Should I convert to Euro's, > > ozs of > > gold, bbl light-sweet crude?), > > Steve in Memphis > > > > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > > Of Wills, > > Steve > > Sent: Thursday, March 13, 2008 11:26 AM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - RBv8, Vista, CROSSTAB Abort ... > > > > Any ideas on why I could be having aborts from RBase when I > > execute a > > CROSSTAB at the R>? > > > > I had a problem with this once and thought it was because I > > was running > > it against a reasonably complex VIEW. This is against a table > > with only > > 400-or-so rows. > > > > > > Thanks, > > Steve in Memphis > > > > > > > > J. Stephen Wills > > Program Manager, Research Informatics > > Office of the Vice Chancellor for Research > > University of Tennessee Health Science Center > > 62 S. Dunlap, Suite 400 > > Memphis, TN 38163 > > Office: 901-448-2389 > > FAX : 901-448-7133 > > > > > > > > > Jim Bentley > American Celiac Society > [EMAIL PROTECTED] > tel: 1-504-737-3293 > > > > > ____________________________________________________________________________________ > Never miss a thing. Make Yahoo your home page. > http://www.yahoo.com/r/hs > > >
