HI Jerome,

a very easy option is the following:

In the <READ> trigger,
transfer your date to a hidden numeric nondb-sortfield;
fill it with maxvalue when a NULL date is encountered

and run a SORT of the entity afterwards.

Another Option is to create a VIEW of your entity
and use the CREATE VIEW statement to
replace the NULL-date with some maximum value.

Success, Uli

A very old "dirty trick" is the following from 1995

you are database dependend, but this should not be a problem.

Success, Uli

++++++++++ o-< ++++++++++++++++++++++++++

In Informix SQL it is possible to sort a result set by a substring:
  SELECT NAME FROM EMP ORDER BY NAME[5,10] works fine.

Unfortunately uniface (5.2.f) doesn't allow a   read order by "NAME[5,10]"
So we tried some dirty tricks using the where-clause:
  read where "text" causes uniface to generate a sql statement like
  SELECT ... FROM ... WHERE (text)
The following strange looking uniface statement builds a hitlist sorted by a substring:
  read where "0=0) order by NAME[5,10] --"
This forces uniface to construct   SELECT ... FROM ... WHERE (0=0) order by NAME[5,10] --)
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
The '--' starts a comment in Informix SQL (as it does in Oracle SQL, I think) and is needed to eliminate the final parenthesis.
I hope this will be helpful to someone else.

Greetings!    Dirk Willecke

Address: Datenzentrale Schleswig-Holstein, z.Hd. Dirk Willecke,        Altenholzer Str. 10-14, 24161 Altenholz, Germany

Phone: ++49 431 3295 626

Thanks Uli, i'll test it !

Hi,

I think, this is quite stupid thing in Uniface :(. We have u_where for Uniface-like conditions (or u_condition) and we have where for DBMS-dependant where clause. Unfortunately there is only one "order by" statement, which is Uniface-specific and very very limited. This should be in fact u_order_by, IMHO, to distinguish it from real DBMS order by clause.

I believe, this is just a great wish to be put on the wishlist, to have the ability to use DBMS-dependant order by clause, since, for example in Oracle, you could use order by "DATE_field desc nulls first". Very easy. (And this "order by" clause driven by DB is much more powerful, you could use pseudo-columns and/or functions etc.)

Solution by Uli is indeed nice and woking, but results in completing hitlist and fetching all data to a component, which might be very unpleasant.

Just my 2 cents :-)
Zdenek