[RBASE-L] - RE: ORDER BY clauses in Views: Clarification

[Bruce Chitiea]

As I move from crawl to walk, I'm using views beneath variable forms and as feedstock for R:Charts. I'll run without Order Bys and see what happens.

Thanks Jim, Alistair, Bill and Dennis.

Bruce

-------- Original Message --------
Subject: [RBASE-L] - RE: ORDER BY clauses in Views: Clarification
From: Dennis McGrath <dmcgr...@qmiusa.com>
Date: Thu, August 09, 2012 9:08 am
To: rbase-l@rbase.com (RBASE-L Mailing List)

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

dmcgr...@qmiusa.com

From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] 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 <bdown...@downallconsulting.com>
Date: Wed, August 08, 2012 11:22 am
To: rbase-l@rbase.com (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 <jamesbentley...@yahoo.com> 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 <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