Again thank you for the suggestion. The change from spelling the words average, minimum and maximum to avg, min and max cleared the error.
but I am curious as to why the functions would not work spelled out? In one instance of checking the view code, R:Style (?) converted my MIN to minimize and was giving me both an error (illegal database name) and minimized my file? that is why i decided to spell them out...and I had thought that was good practice anyhow? Happy FRIDAY, hope everyone has great excitment ahead for the weekend tom ----- Original Message ----- From: "David M. Blocker" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Thursday, August 19, 2004 7:28 PM Subject: [RBG7-L] - Re: illegal column specification?????? > Tom > > Try just the SELECT to narrow it down - to see if it's the CREATE VIEW part > or the SELECT that's the error. If the SELECT, remove one expression at a > time until you get rid of the error message - then you'll know what part of > the command is the problem > > David Blocker > [EMAIL PROTECTED] > 781-784-1919 > Fax: 781-784-1860 > Cell: 339-206-0261 > ----- Original Message ----- > From: "Thomas Eldred" <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> > Sent: Thursday, August 19, 2004 3:56 PM > Subject: [RBG7-L] - Re: illegal column specification?????? > > > > Thank you Larry and Albert, > > > > I have fixed the missing plus and enclosed my expresion in a min() so that > > it looked both as MIN((.....)) and MIN(...) > > > > I am still getting a syntax error. > > > > Code now reads(for all three tables) > > > > CREATE VIEW electcommrben + > > (typecount, employtype, + > > avgchair, avgvc,avgmem, avgcommr, minboard, + > > maxboard, avgboard) + > > AS SELECT + > > COUNT(*),MIN((selectionmethod+' '+ service)) , + > > AVERAGE(chairmansalary) , + > > AVERAGE(vicechairsalary) , + > > AVERAGE(membersalary) ,+ > > AVERAGE((chairmansalary+vicechairsalary+membersalary)/3), + > > MIN(chairmansalary+vicechairsalary+membersalary), + > > MAXIMUM(chairmansalary+vicechairsalary+membersalary), + > > AVERAGE(chairmansalary+vicechairsalary+membersalary) + > > FROM miscommrs WHERE mcyear = 2002 AND selectionmethod = 'elected' AND > > service = 'Part time' > > > > Thank you again > > > > tom > > > > ----- Original Message ----- > > From: "Lawrence Lustig" <[EMAIL PROTECTED]> > > To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> > > Sent: Thursday, August 19, 2004 2:51 PM > > Subject: [RBG7-L] - Re: illegal column specification?????? > > > > > > > The problem is that in each view you are including a > > > single non-aggregated return value (the second field, > > > which is a concatenation of two columns in the > > > database). > > > > > > If you include aggregate and non-aggregate fields in > > > your result set you must include a GROUP BY clause in > > > the select including all non-aggregate fields. > > > > > > In your case, since I think you're after a single row > > > in your result set limited by the WHERE clause, just > > > remove the non-aggregate expression or, if you must > > > have it, encase it in a call to MIN(). > > > > > > -- > > > Larry > > > > > > > > > > > >
