Path: utzoo!utgpu!attcan!uunet!lll-winken!ames!lll-lcc!pyramid!infmx!greggy
From: greggy@infmx.UUCP (greg yachuk)
Newsgroups: comp.databases
Subject: Re: INFORMIX Sorting Question
Message-ID: <732@infmx.UUCP>
Date: 6 Jan 89 17:27:30 GMT
References: <465@gandalf.littlei.UUCP>
Reply-To: greggy@infmx.UUCP (greg yachuk)
Organization: Informix, Menlo Park, Ca. U.S.A.
Lines: 41

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