marko@apple.i.intel.com.ogc.edu (01/06/89)
I have Informix 4gl and Sql installed on a Xenix workstation. The users requested some changes and additions all of which are complete except one. I am wondering if anyone out there knows of a way to give the user an ad hoc sorting capability in Informix. The scenerio is this. The report they want to sort has 13 columns in it. The users want to be able to sort on 7 of those columns. Now comes the rub. They want to sort differently for each user. For example, user1 may want to sort on columns 1, 3 and 9 in that order. User2 may want to sort on columns 13, 12, 10 and 2 in that order. So forth and so on. Is there a simple way to do this? I have read the manuals and do not see a way. I know I could set up a menu and the corresponding functions to give each user the kind of sort they want, but that would restrict them to a small set of capabilities-if we want to practical. Any help or advice will be greatfully appreciated. Mark O'Shea SDA
greggy@infmx.UUCP (greg yachuk) (01/07/89)
In article <465@gandalf.littlei.UUCP> marko@apple.i.intel.com (Mark O'Shea) writes: >The scenerio is this. The report they want to sort has 13 columns in it. The >users want to be able to sort on 7 of those columns. Now comes the rub. They >want to sort differently for each user. For example, user1 may want to sort >on columns 1, 3 and 9 in that order. User2 may want to sort on columns 13, >12, 10 and 2 in that order. So forth and so on. > >Is there a simple way to do this? Simple? Not really, but here is one way of doing it. You could use a form with one or more fields to specify which columns and the order (either by name or by column number). Concatenate with the select part, prepare and use. DEFINE ord1 CHAR[20], ord2 CHAR[20], ... INPUT ord1, ord2, ord3, ord4, ord5, ord6, ord7, ord8 FROM ... LET query = "select * from tables order by ", ord1, ord2, ord3, ord4 ... PREPARE query_prep FROM query DECLARE query_cursor CURSOR FOR query_prep FOREACH query_cursor INTO query_rec.* ... END FOREACH A couple of things to watch out for: the field names or numbers in the "order by" clause must be separated by commas, but cannot have extraneous trailing commas. This means that you must check each one and get rid of empty ones, but insert commas before concatenating. We have a limit of 8 columns that may participate in an ORDER BY, so you don't need to get any more than that. I hope this gets you going. Also, 4GL is not my "native" language. There may be more elegant ways of doing this. This is just my "off-the-top-of-head" solution. >Mark O'Shea >SDA -greg Greg Yachuk Informix Software Inc., Menlo Park, CA (415) 322-4100 {uunet,pyramid}!infmx!greggy why yes, I DID choose that login myself
marko@apple.i.intel.com.ogc.edu (01/11/89)
Greg, Thanks for the help. I tried to send you mail to say thanks, but we are having trouble getting that out. Mark O'Shea SDA