The real issue is that an ordered view is not editable, or should not be. If you are going to edit (or browse) a dataset from a view, the view and any sub-views should not have order clauses.
But an ordered view is useful for many things so it is not verboten. Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: Thursday, August 09, 2012 10:51 AM To: RBASE-L Mailing List Subject: [RBASE-L] - ORDER BY clauses in Views: Clarification Bill: Thanks, and noted. For now, I'll draw a distinction between an RBase-only world and one requiring collaboration with other database products. I stipulate that in the collaborative universe, staying true to the SQL standard is the prudent course. In R:World, View Order-Bys are allowed. I can always draw them ineptly, and perhaps in this case build in my own trouble. Perhaps there's no advantage, indeed only a waste of clock cycles, even in 'correctly' ordering 'lower level' precursor views such as I have here; in which case I'm happy to discard them. But does your recommendation apply to the 'top level' view in this R:World case? Is there ANY advantage to ordering a view? Or do we optimally leave ordering to the routines drawing upon the view? I appreciate your clarity. Bruce -------- Original Message -------- Subject: [RBASE-L] - Re: BROWSE Window View Wierdness From: Bill Downall <[email protected]<mailto:[email protected]>> Date: Wed, August 08, 2012 11:22 am To: [email protected]<mailto:[email protected]> (RBASE-L Mailing List) I would eliminate the ORDER BY clauses from the view definitions. R:BASE allows it, but the SQL standard does not. And in this case, you may have conflicting order by clauses. Bill On Wed, Aug 8, 2012 at 8:57 AM, James Bentley <[email protected]<mailto:[email protected]>> wrote: Bruce, Problem may be in vwMonthlyBudgetIncome. I am not sure about following statement: "SELECT fymid,unitcount,SUM(uaamt),(SUM(uaamt) * unitcount) +" since your group by refers to "fymid,unitcount" i am not sure how RBase treats the use of two references to "unitcount" in the above select statement. you might consider using: "SELECT fymid,unitcount,SUM(uaamt),(SUM(uaamt) * MAX(unitcount)) +" instead. I also note you are inconsistent in defining your views: CREATE VIEW vwMonthlyBudgetIncome + (fymid,unitcount,unitdues,budgetincome) AS + CREATE VIEW vwMonthlyDepositTotal AS + CREATE VIEW vwMonthlyIncomeSummary AS + the last two do not name columns as does the first one. I prefer to use the syntax of naming all columns as was done in vwMonthlyBudgetIncome. Jim Bentley, American Celiac Society 1-504-737-3293<tel:1-504-737-3293> ________________________________ From: Bruce Chitiea <[email protected]<mailto:[email protected]>> To: RBASE-L Mailing List <[email protected]<mailto:[email protected]>> Sent: Tuesday, August 7, 2012 7:03 PM Subject: [RBASE-L] - BROWSE Window View Wierdness All: Typo aside, to simplify the message, 'vwGoodToGo' was used to alias view: vwMonthlyIncomeSummary: VIEW: vwMonthlyIncomeSummary CREATE VIEW vwMonthlyIncomeSummary AS + SELECT t1.fymid, deptotal, fyyrnum, + cmyrnum, cmyrabbr, unitdues, unitcount, + budgetincome, (deptotal-budgetincome) AS depvariance + FROM vwmonthlydeposittotal t1, + vwmonthlybudgetincome t2 + WHERE t2.fymid = t1.fymid + ORDER BY t1.fymid ****************************** Which draws on two precursor views: VIEW: vwMonthlyDepositTotal CREATE VIEW vwMonthlyDepositTotal AS + SELECT fymid,fyyrnum,cmyrnum,cmyrabbr, SUM(depamt) AS deptotal + FROM deposit t1,fymonth t2,zcalmonth t3 + WHERE + t2.fymid = t1.fymid AND + t2.cmid = t3.cmid + GROUP BY fymid,fyyrnum,cmyrnum,cmyrabbr + ORDER BY fymid ****************************** and: VIEW: vwMonthlyBudgetIncome CREATE VIEW vwMonthlyBudgetIncome + (fymid,unitcount,unitdues,budgetincome) AS + SELECT fymid,unitcount,SUM(uaamt),(SUM(uaamt) * unitcount) + FROM ua2fym t1,uc2fym t2 + WHERE t2.fymid=t1.fymid + GROUP BY fymid,unitcount + ORDER BY fymid ****************************** 1. All three views perform properly at the R> Prompt. 2. vwMonthlyDepositTotal and vwMonthlyBudgetIncome both perform properly in BROWSE windows 3. Only vwMonthlyIncomeSummary fails in BROWSE windows as described in the earlier message with: 'Invalid Cursor State (2645)' 'The requested expression does not exist (2144)' Thanks for your help. Bruce
