3 Replies Latest reply on Dec 9, 2014 9:15 AM by Steven Hawkins

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

    Sanjeev Gour Novice

      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.