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]>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 > ------------------------------ > *From:* Bruce Chitiea <[email protected]> > *To:* RBASE-L Mailing List <[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 > > > >
