[RBASE-L] - Re: BROWSE Window View Wierdness

[Bruce Chitiea]

Jim:

1. Consistency note: taken.

2. I'll try 'MAX(unitcount)' and report back.

Thanks

Bruce

-------- Original Message --------
Subject: [RBASE-L] - Re: BROWSE Window View Wierdness
From: James Bentley <jamesbentley...@yahoo.com>
Date: Wed, August 08, 2012 5:57 am
To: rbase-l@rbase.com (RBASE-L Mailing List)

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 <rby...@safesectors.com>
To: RBASE-L Mailing List <rbase-l@rbase.com>
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