> I tried select. still no luck
That is a proper exception indicating that the function is recognized, so this is progress. However the next clarification is that depending upon the Teiid version and the translator, Teiid may not be able to simply push a scalar execution down. You need to be on Teiid 8.5 and later [TEIID-2571] Generally allow must pushdown function evaluation - JBoss Issue Tracker and you would need to use a specific translator that knows how the function should be invoked by itself at the source (using values(), select, or call, etc.). In this case JDBC simple will not allow just the function to be pushed by itself. However if you just associate the function with a source table, the pushdown will also occur as expected:
select ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss')) from ProficyODBCSrc.sometable