Hi Albert, I hadn't remembered that and you're right, but (there has to be a but) the INTO Var part doesn't work as it returns too many rows.
SELECT (LISTOF(Database)) INTO vDBList FROM MultiDatabase GROUP BY Database ORDER BY 1=DESC fails, as does: SELECT Database, (LISTOF(Database)) INTO vX, vDBList FROM MultiDatabase GROUP BY Database ORDER BY 2=ASC Creating a temp view is so simple to sort the rows first that it's hardly worth trying to get it to work in the single command but it _is_ fun!! Regards, Alastair. ----- Original Message ----- From: "Albert Berry" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Friday, December 20, 2002 2:52 PM Subject: [RBASE-L] - Query ListOf command > I am afraid you have fallen victim to a syntax error. To sort by a function, one uses the number of the column in the list, not the column name. I, for example, have used this one successfully: > > SELECT CustPart,(LISTOF(InvLocation)) + > FROM Inventory + > GROUP BY CustPart + > ORDER BY 2 ASC > ^ > Note this | which is how one tells SQL languages to sort on a computed value. > > This is also true of: > SELECT CustPart,COUNT(*) + > FROM Inventory + > GROUP BY CustPart + > ORDER BY 2 DESC > SELECT CustID,SUM(Sales) + > FROM Invoices + > GROUP BY CustID + > ORDER BY 2 DESC > etc. > > > Ramsour Mike <[EMAIL PROTECTED]> wrote: > > >Thanks, Dennis. Your suggestion got me to thinking further about this and I > >think I boiled it down to its simplest form. I modified my program > >accordingly: > > > >SET VAR vdu_prog_list TEXT > >-- > >PROJECT TEMPORARY temp_prog_list FROM prog_results USING run_type,progname + > >ORDER BY run_type,progname WHERE prog_run = 'Y' > >-- > >SELECT (listof(progname)) INTO vdu_prog_list FROM temp_prog_list > > > >Works for me. > > > >Thanks again. > > > >Mike Ramsour > > > >-----Original Message----- > >From: Dennis McGrath [mailto:[EMAIL PROTECTED]] > >Sent: Friday, December 20, 2002 8:54 AM > >To: [EMAIL PROTECTED] > >Subject: [RBASE-L] - Query ListOf command > > > > > > > > > > > > > >A temporary view to do the sorting would also work. > > > >--Dennis McGrath > > > > > > > >-----Original Message----- > >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ramsour > >Mike > >Sent: Friday, December 20, 2002 7:16 AM > >To: RBASE-L Mailing List > >Subject: [RBASE-L] - Query ListOf command > > > > > >Alastair: > > > >Coincidentally I just discovered today that the ORDER BY clause does not > >appear to work with the LISTOF function. I use this function to create a > >variable which is a list of programs I want to run. I loop through the > >variable to run the programs. As a work around I create a temporary table > >into which I insert the program names from a table in the desired order. I > >then use the LISTOF function to create my variable from the ordered list of > >programs. > > > >At least you know you've got company. :) > > > >Mike Ramsour > >voice: 740-829-4340 > > > > > >-----Original Message----- > >From: Alastair Burr [mailto:[EMAIL PROTECTED]] > >Sent: Friday, December 20, 2002 2:56 AM > >To: [EMAIL PROTECTED] > >Subject: [RBASE-L] - Query ListOf command > > > > > > > > > > > > > >This got lost in the mists of time in the sonetmail hiccup: > > > > Does anybody agree with me that this fails: > > > > SELECT (LISTOF(Extension)) INTO vResponse FROM FileExtensions order by > > Extension > > > > but these work: > > > > SELECT (LISTOF(Extension)) INTO vResponse FROM FileExtensions > > > > SELECT (LISTOF(Extension)) INTO vResponse FROM FileExtensions where > > Extension is not null > > > > SELECT (LISTOF(Extension)) INTO vResponse FROM FileExtensions where > > Extension is not null order by Extension > > > > (They're all single-line commands.) > > > > If so, should I expect the first method to work or not? > > > > > >Since then it has been discovered that the third "working" example doesn't > >appear to actually sort but doesn't give the error message that the > >non-working example does. > > > >A easy work-around of using a (temp) view to re-order the list has also been > >suggested and the need, or otherwise, of two pairs of parenthesis has been > >questioned. (R:Syntax shows both!) > > > >See what excitement you've missed while you were away! Welcome back. > >Regards, > >Alastair. > > > >---------------------------------- > >A D B Burr, > >St. Albans, UK. > >---------------------------------- > >[EMAIL PROTECTED] > >---------------------------------- > > > > > > > -- > Albert Berry > Full Time Consultant to > PSD Solutions > 350 West Hubbard, Suite 210 > Chicago, IL 60610 > 312-828-9253 Ext. 32 > > > __________________________________________________________________ > The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp > > Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ >
