Order in an entity

Author: bignonj@gmail.com (Bignon)



I've an based entity that displays occurence in descending order of a date field.

--> read u_where ..... order by "DATE_field descending"

But I would like to view the occurences with DATE_field = null in first postion, is it possible ?

ex :

1 positon :  Date_field1 = null

2 position : Date_field2 = null

3 position : Date_field3 = 15/04/10

4 position : Date_field4 = 13/04/10

5 position : Date_field5 = 05/02/10


Thanks for your help








  1. 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

    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. Thanks Uli, i'll test it !


    Author: Bignon (bignonj@gmail.com)
  3. 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 :-)

    Author: sochaz (zdenek.socha@fullsys.cz)
  4. 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


    Author: ulrich-merkel (ulrichmerkel@web.de)