ORDER BY SUBSTRING

[DK...@dzsh.landsh.dbp.de]

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

Internet: dk...@dzsh.landsh.dbp.de
Address: Datenzentrale Schleswig-Holstein, z.Hd. Dirk Willecke,
Altenholzer Str. 10-14, 24161 Altenholz, Germany
Phone: ++49 431 3295 626
------------ or private: --------------------------------------
Internet: Dirk.W...@kiel.netsurf.de
Address: Dirk Willecke, Buensowstr. 13, 24106 Kiel, Germany
Phone: ++49 431 332898