Can we avoid trimStrings in ORDER BY or use alias instead?

[sanjeev.gour]

Hi-

.

We are using the trimStrings translator property to trim CHAR/NCHAR columns. Setting this property applies trim to the column in the ORDER BY clause also and produces a query of the following form-

SELECT rtrim(g_0.\"EVTSYSID\") FROM \"CMGRD1\".\"VIEWSYSACCESS\" AS g_0 WHERE g_0.\"EVTUTCTOD\" >= {ts '2014-12-04 05:30:00.0'} AND  g_0.\"EVTUTCTOD\" <= {ts '2014-12-04 06:00:00.0'} ORDER BY rtrim(g_0.\"EVTSYSID\")

while this works fine in DB2, one of the in-house database fails to work with it. The database works fine if we provide an alias to the column like below-

SELECT rtrim(g_0.\"EVTSYSID\") AS C1 FROM \"CMGRD1\".\"VIEWSYSACCESS\" AS g_0 WHERE g_0.\"EVTUTCTOD\" >= {ts '2014-12-04 05:30:00.0'} AND g_0.\"EVTUTCTOD\" <= {ts '2014-12-04 06:00:00.0'} ORDER BY C1

Is there a way by which we can control where trimStrings applies or if we can hint it to use the alias?

Thanks.

Sanjeev.

[shawkins]

> Is there a way by which we can control where trimStrings applies or if we can hint it to use the alias?

The only time we would not be using the alias for an ordering is if it is the ordering is unrelated or if the translator does not support select expressions nor inline views.  What capabilities does your translator have?

[sanjeev.gour]

Hi Steve-

The translator did not support select expressions, although it could do that. Enabling that solves the issue.

Appreciate your help.

Thanks.

[shawkins]

If there is a need for a specific select alias capability, rather than the more general select expression just let us know.