read order by... NULLS LAST

Author: mroesch@arz-emmendingen.de (roesch)

Hello, in my use case I need the NULLS LAST option for the ORDER BY clause. Our Informix Database Server offers this feature (it's part of ANSI SQL), but Uniface (we are working with 9.6) seems not to support it. Is this related to the database driver or the uniface language? Is there any possibility to sort in the read trigger with the NULL values at the end? Cheers, Michael

4 Comments

  1. roesch said Hello, in my use case I need the NULLS LAST option for the ORDER BY clause. Our Informix Database Server offers this feature (it's part of ANSI SQL), but Uniface (we are working with 9.6) seems not to support it.

    Hi Michael, perhaps you remember the old trick using the WHERE statement to add any SQL commands you like: As Uniface encloses the WHERE string in parentheses, the trick is to ad a closing parenteses, append additional commands and a final comment. IIRC, for ORACLE it was something like: WHERE "1=1) order by substr(FIELD1,3) --" HIH, greetings from a sunny 17° Frankfurt/Germany evening, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. Hello, as for Oracle DB you can set the driver option in the .asn file: nulls last

    [DRIVER_SETTINGS] USYS$ORA_PARAMS = nulls last

    Unfortunately this is then used for the whole application and you can't change it. And I don't see this option for Uniface Informix Connector Options. :( BTW the "order by" clause in Uniface is very limited... I have created a wish about this, but no improvement since then. Kind regards, Zdenek Socha


    Author: sochaz (zdenek.socha@fullsys.cz)
  3. roesch said Is there any possibility to sort in the read trigger with the NULL values at the end? Cheers, Michael

    Hi Michael Quick&Dirty [and sometimes not performantConfused] Add an extra, non-database field to the entity -- READ-Trigger -- $01 = FIELD.ENTITY ; Fetch, just in case IF(FIELD.ENTITY=="")   NDB_FIELD.ENTITY = "~~~~~~~~~" ; Or any other value thats higher then all values ELSE   NDB_FIELD.ENTITY = FIELD.ENTITY ENDIF -- RETRIEVE -- retrieve/e "ENTITY" sort/e "ENTITY","NDB_FIELD"


    Author: istiller (i2stiller@gmx.de)
  4. Hi Michael, Just an idea: a simple solution (if applicable) could be to split occurrence set into two stages: - first stage NOT NULL occurrences - second stage NULL occurrences Hope it helps. Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)