Bernie, I have seen some of your replies, but I'm not sure which one might have gotten bounced...care to send it again? Most of the answers provide ways for getting the data, but the real trick (if possible) is to create a view that would show updated data each time the view is looked at. The ideal display of data would have.... service---last date----last price----max price----min price----most common price.
It's easy to create a declare cursor routine to extract the data and load another table, but a view that had the same data would "update itself" each time it was looked at. Can it be done? And if so.....would it take so much processing power that it would be slow, and therefore best accomplished after hours with a declare cursor routine?? TIA! Mike Sinclair Bernie Corrigan wrote: > Mike - > > Did my reply to this make the list? My email program crashed during > the send and the original disappeared here and I have no record of receiving > it back from the list. Please let me know. > > Thanks, > Bernie > -------------------------------------- > At 02:28 PM 6/2/2002 -0400, you wrote: > >Ok, perhaps you could show me how to do this with a single example. A > salesman is trying > >to see the prices that other salesman have sold a particular service for. > The table with > >the raw data has 3 columns, rdate_ , service, and price. In the raw data > table, there are > >about 100,000 rows, 100 different services, and dates starting from 2 years > ago. > >So.......how does the saleman quickly figure out the last price, the max > price, the least > >price, and the most common price that the chosen service has sold for? Can > that data be > >shown in a view some how? > > > >Mike Sinclair > > > >Bernie Corrigan wrote: > > > >> You handle that with the WHERE and GROUP BY clauses in the view definitions. > >> What do you > >> mean "recreate the data"? You are looking for statistics on welldefined > >> subsets of your > >> data. Those subsets can be specified with WHERE and/or GROUP BY clauses. I > >> think the > >> process you describe can be done without using a cursor. There are really a > >> lot of > >> different ways you can go here. > >> > >> At 05:59 PM 6/1/2002 -0400, you wrote: > >> >What I have is a table with about 100,000 rows of data. What I did was use > >> a program at > >> >the end of the day to calculate the new min, max, last, and common for each > >> service, but I > >> >used a declare cursor routine to do it. The only advantage, is that the > >> data is instantly > >> >available, but it does take some time at the end of the day to run that > >> file. From what > >> >I've been reading, there might be a to create a view that would just > >> recreate the data on > >> >the fly as needed for any given service, but maybe that's too much to > ask for. > >> > > >> >Mike Sinclair > >> > > >> >Bernie Corrigan wrote: > >> > > >> >> Since we are dealing with R:Base I wrote my second example in R:Base. My > >> >> first example was more like what you have below. The ANSI SQL SELECT > >> >> statement puts the query results into a temporary results table and it > is up > >> >> to the programming language to furnish a way to get what you want out of > >> >> that table. R:Base provides some fairly easy to use methods. Other > >> >> languages also have the INTO clause. This includes DB2 and Oracle among > >> >> others although it can mean different things in different languages. > >> >> > >> >> At 02:03 PM 6/1/2002 -0700, you wrote: > >> >> >Bernie Corrigan wrote: > >> >> > > >> >> >> DROP VIEW Lcount > >> >> >> CREATE VIEW Lcount AS SELECT COUNT(loc) AS lcount, loc FROM encountr > >> >> GROUP BY loc > >> >> >> SET VAR vlmaxcount INT = (MAX(lcount)) IN Lcount > >> >> > > >> >> >SELECT MAX(lCount) + > >> >> > INTO vlMaxCount + > >> >> > FROM lCount > >> >> > > >> >> >> SET VAR vlmode INT = loc IN Lcount WHERE lcount = .vlmaxcount > >> >> > > >> >> >SELECT Loc + > >> >> > INTO vlMode + > >> >> > FROM lCount + > >> >> > WHERE lCount = .vlMaxCount > >> >> > > >> >> >Bernie, your syntax isn't SQL. The > >> >> >syntax I've got is at least partially > >> >> >SQL. There is no "INTO VarName" > >> >> >clause in SQL but that "IN" stuff is > >> >> >really old fashioned. That stuff should > >> >> >have gone out when you started in > >> >> >2.11. I know you're going to disagree > >> >> >with me but... ...it ain't SQL. > >> >> > > >> >> >Scott > >> >> >==== > >> >> > > >> >> > > >> >> > > >> >> > > >> >> >The rStreet List is dedicated to database application development, > >> >> >R:BASE, PHP, Visual Basic etc... > >> >> > > >> >> >All products mentioned on this list are the trademarks of their > resepective > >> >> companies. > >> >> > > >> >> >Post: [EMAIL PROTECTED] > >> >> >Unsubscribe: [EMAIL PROTECTED] > >> >> >http://groups.yahoo.com/group/rStreet/ > >> >> > > >> >> >Scott Salisbury - [EMAIL PROTECTED] > >> >> > > >> >> >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ > >> >> > > >> >> > > >> >> > > >> >> > >> >> > >> >> The rStreet List is dedicated to database application development, > >> >> R:BASE, PHP, Visual Basic etc... > >> >> > >> >> All products mentioned on this list are the trademarks of their > >> resepective companies. > >> >> > >> >> Post: [EMAIL PROTECTED] > >> >> Unsubscribe: [EMAIL PROTECTED] > >> >> http://groups.yahoo.com/group/rStreet/ > >> >> > >> >> Scott Salisbury - [EMAIL PROTECTED] > >> >> > >> >> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ > >> > > >> > > >> > > >> >The rStreet List is dedicated to database application development, > >> >R:BASE, PHP, Visual Basic etc... > >> > > >> >All products mentioned on this list are the trademarks of their resepective > >> companies. > >> > > >> >Post: [EMAIL PROTECTED] > >> >Unsubscribe: [EMAIL PROTECTED] > >> >http://groups.yahoo.com/group/rStreet/ > >> > > >> >Scott Salisbury - [EMAIL PROTECTED] > >> > > >> >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ > >> > > >> > > >> > > >> > >> ------------------------ Yahoo! Groups Sponsor ---------------------~--> > >> Tied to your PC? Cut Loose and > >> Stay connected with Yahoo! Mobile > >> http://us.click.yahoo.com/QBCcSD/o1CEAA/sXBHAA/PhFolB/TM > >> ---------------------------------------------------------------------~-> > >> > >> The rStreet List is dedicated to database application development, > >> R:BASE, PHP, Visual Basic etc... > >> > >> All products mentioned on this list are the trademarks of their > resepective companies. > >> > >> Post: [EMAIL PROTECTED] > >> Unsubscribe: [EMAIL PROTECTED] > >> http://groups.yahoo.com/group/rStreet/ > >> > >> Scott Salisbury - [EMAIL PROTECTED] > >> > >> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ > > > >================================================ > >TO SEE MESSAGE POSTING GUIDELINES: > >Send a plain text email to [EMAIL PROTECTED] > >In the message body, put just two words: INTRO rbase-l > >================================================ > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > >In the message body, put just two words: UNSUBSCRIBE rbase-l > >================================================ > >TO SEARCH ARCHIVES: > >http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
