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



      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?