The correct syntax is WHERE before GROUP, having after GROUP. I am certain this will come back to bite you if you have it in the wrong place.
Tom Grimshaw <[EMAIL PROTECTED]> wrote: >I'm with Steve on this one. � I have reasonably concrete guidelines >I set for my programmers for many parameters. � Another advantage is >that if the code is laid out in a consistent style it make visual >checking just that much easier. > >Which leads me to a question. � In the reply I posted I put the >WHERE clause before the GROUP BY as I copied the format from my >working view rebuilding app file. � I noticed that most other >people have the WHERE _after_ the GROUP BY. � On checking the >on-line help the syntax is displayed as: >WHERE... sub-SELECT... AS... GROUP BY... > >Is R:BASE equally tolerant of each syntax? >Or is one going to lead to a sin tax error down the road? > >At 09:10 25/06/02 -0500, you wrote: >>Dan, everyone's given you the correct answer, it seems, so, let me add to >>the replies something about style/structure that I have found to be very, >>very helpful to me, especially when it comes to situations like this. >> >>CREATE VIEW + >> � vMyView + >> � (Firstdate, + >> � �LastDate, + >> � �TotalOrders, + >> � �TotalRevenue, + >> � �ClientNum) + >>AS + >>SELECT + >> � (MIN(ORDDate)), + >> � (MAX(ORDDate)), + >> � (COUNT(ORDDate)), + >> � (SUM(Total)), + >> � ClientNUM + >>FROM + >> � ORDERS + >>GROUP BY + >> � ClientNUM + >>WHERE + >> � Cancelled <> 1 >> >>I know, this might seem like a lot of nothin' f/nothin' in return. �However, >>this style/structure helps me in mentally parsing the statement(s), >>especially when I haven't looked at the code in a while. �And that little >>bug-a-boo of having an un-equal number of fields in the named view-fields >>versus the SELECT component is readily addressed here - I, for one, make >>this error regularly - by simply counting the number of named fields versus >>the number of SELECTed fields. �Additionally, it helps me to make sure that >>the named fields match the SELECTed fields, not just in count, but in >>meaning. >> >>Admittedly, too much style/structure can be counter-productive. �And I, like >>most folks, test statements at the R> before placing them in a command file. >>However, like so many other things we dis-like/tend not to do in >>implementing code, the maintenance costs of these "short-cuts", based on my >>experience, far exceed the marginal/incremental costs of things like >>structure, comments, modularization, etc. �And, if your code/implementation >>is any good, it'll take on a life/viability its own, meaning that those >>un-necessary maintenance costs will continue to be incurred, again, and >>again, and again ... >> >>Well, I'm sure you get the picture. >> >>Later, >>Steve in Memphis >> >> > -----Original Message----- >> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On >> > Behalf Of Dan >> > Sent: Monday, June 24, 2002 5:34 PM >> > To: [EMAIL PROTECTED] >> > Subject: Report View Question >> > >> > >> > Hi all. I have the following view: >> > >> > select (min(orddate)) as FirstDate,(max(OrdDate)) as >> > LastDate,(count(OrdDate)) >> > AS TotalOrders,(sum(Total)) as TotalRevenue,clientnum from orders group by >> > clientnum where cancelled <> 1 >> > >> > on which I'm trying to design a report. However, when I use the report >> > designer, only the column <clientnum> appears available. What happened to >> > the other columns? >> > >> > Thanks, >> > Dan >> > >> > >> > >> > >> > >> > ================================================ >> > 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/ > >Warmest regards, > > >Tom Grimshaw >coy: � �Just For You Software >tel: � �612 9552 3311 >fax: � �612 9566 2164 >mobile: 0414 675 903 > >post: � PO Box 470 �Glebe �NSW �2037 �Australia >street: 3/66 Wentworth Park Rd �Glebe �NSW �2037 > >email: �[EMAIL PROTECTED] >web: www.just4usoftware.com.au > >"... the control of impulse -- is the first principle of civilization."-- >Will Durant, >Pulitzer Prize winning philosopher, writer and historian > >the most needed product in the world can be found at >www.thewaytohappiness.org > >This email and any files transmitted with it are confidential to the >intended recipient and may be privileged. If you have received this email >inadvertently or you are not the intended recipient, you may not >disseminate, distribute, copy or in any way rely on it. Further, you should >notify the sender immediately and delete the email from your computer. >Whilst we have taken precautions to alert us to the presence of computer >viruses, we cannot guarantee that this email and any files transmitted with >it are free from such viruses. > >================================================ >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/ > __________________________________________________________________ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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/
