John - have you tried changing the Select Type in a Search Action to Summary? I think Witango will do this.
> -----Original Message----- > From: John Newsom [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 25, 2004 3:32 PM > To: [EMAIL PROTECTED] > Subject: RE: Witango-Talk: another sql puzzler > > > Duh! Thanks, Alan. Worked like a charm. Now I wonder why I > need to use a direct sql statement. It seems like the search > action ought to be able to produce simple sql like that! > > John > > -----Original Message----- > From: Alan Wolfe [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 25, 2004 1:32 PM > To: [EMAIL PROTECTED] > Subject: Re: Witango-Talk: another sql puzzler > > > Hi John, > > In a join like below, you have to have a column that links > the 2 tables together, or else it will select all data from t > shirts for every student or something silly like that and > thats why your getting such huge counts. > > if tblStudents2003 has a studentid you would do it like this: > > select count(a1.studentid) as numorders, a1.studentid > from dbo.maydayShirts a1, dbo.tblStudents2003_4 b1 > where b1.yog = '2004' > and a1.studentid=b1.studentid > group by a1.studentid > order by numorders desc > > Adding the a1.studentid=b1.studentid clause (or something > else which links the 2 tables directly together like that) is > what should do the trick for you. > > Hope that helps! > Alan > > ----- Original Message ----- > From: "John Newsom" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, March 25, 2004 12:48 PM > Subject: Witango-Talk: another sql puzzler > > > I have two tables, one with tshirt orders, one with students, > including year of graduation (yog). I'm trying to get a > count of the number of shirts ordered by each student, > selecting only the current seniors. > > This statement, which joins the two tables, > > > > select count(a1.studentid) as numorders, a1.studentid from > dbo.maydayShirts a1, dbo.tblStudents2003_4 b1 where b1.yog = > '2004' group by a1.studentid order by numorders desc > > produces a bizzare count in the numorders column, where I get > a goofy number in the numorders column. Here is a sample output: > > numorders studentid > 565 5998 > 565 8429 > 339 8249 > 226 8136 > 226 8308 > 226 8342 > 226 7980 > 226 7986 > 226 8059 > 226 8074 > > The other statement relies on a view which joins the two > tables, and works fine. > > > select count(studentid) as numorders, studentid from > dbo.tshirtsyog where yog = '2004' group by studentid order by > numorders desc > > Here is sample output: > > numorders studentid > 5 5998 > 2 5999 > 2 7356 > 2 7516 > 2 7561 > 2 7857 > 2 7868 > 2 7887 > 2 7980 > 2 7986 > > Is the basic lesson that if one is doing a count and a where > clause, do join first? Or is there some sort of sub select > statement I need? > > I gave up trying to use the witango search action, because I > couldn't get even a simple count statement to work! > > Thanks! > > John Newsom > > > -----Original Message----- > From: Alan Wolfe [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 25, 2004 11:48 AM > To: [EMAIL PROTECTED] > Subject: Re: Witango-Talk: Something completely different > > > Hi Gene, > > Could you build an array with the table names and then loop > through the array doing the work for each table (using the > variable in place of where you would normaly have your > constant table name in the DBMS)? > > ----- Original Message ----- > From: "Wolf, Gene" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, March 25, 2004 11:32 AM > Subject: RE: Witango-Talk: Something completely different > > > Scott, no, I could do that and that may be a solution. > What I have, in essence, is a single ODBC with what amounts > to multiple tables within the "database". All of the "tables" > have the exact same structure so I want to loop through all > of the records of one table, list them, then read all of the > records of the next table, list them, etc. > > This is hard to explain but I hope this gives you a better > idea of what I'm trying to do. > > Thanks for your help! > > -----Original Message----- > From: Scott Cadillac [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 25, 2004 2:19 PM > To: [EMAIL PROTECTED] > Subject: RE: Witango-Talk: Something completely different > > > Hi Gene, > > Not that I understand 100% of what you're doing, but I think > you're saying each Excel file has a different ODBC connection, right? > > If you have an array that describes all your Excel file > connections, then you can do a Loop on that and use Variables > for your datasource connections > - then you only need one Search Action and one set of code > (for parsing the > data) for the whole thing. > > See http://xmlx.ca/articles/571.aspx > > Hope this helps. Cheers.... > > Scott Cadillac, > 403-281-6090 ~ [EMAIL PROTECTED] > ------------ > XML-Extranet ~ http://xmlx.ca ~ http://forums.xmlx.ca > Well-formed Programming in C# .NET, Witango, MSIE and XML > ------------ > IExtranet ~ http://iextranet.ca > Witango ~ http://witango.org > EasyXSLT ~ http://easyxslt.ca > IIS Watcher ~ http://iiswatcher.ca > ------------ > > > > > > ________________________________ > > From: Wolf, Gene [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 25, 2004 11:46 AM > To: [EMAIL PROTECTED] > Subject: Witango-Talk: Something completely different > > > > OK, this may be way off the wall but I figured one of you > folks would have tried this, or know it can't be done. I have > a number of excel spreadsheets that I will be reading data > from using Witango. There are about 14 of these sheets and I > have working ODBC links to each one of these. Now, the format > of all of these sheets is exactly the same and I need to be > able to create the exact same reports from each of these > spreadsheets. Is there any way I can change the table I'm > looking at in either a search action or a direct DBMS so I > can loop through a list of table names and create the reports > without duplicating the code for every table? > > Does this make sense to anyone? *laughs* > > Gene Wolf > Business Systems Analyst, TLMN > DRS Optronics, Inc. > 2330 Commerce Park Drive > Palm Bay, Florida 32905 > Phone: 321-309-0685 > E-mail: [EMAIL PROTECTED] > > > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > > > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
