I am not sure IN clause supports executing functions in the query did it fail when you tried less than greater than type
On Fri, Apr 5, 2013 at 7:36 PM, LUTTER, Steffen <steffen.lut...@sap.com>wrote: > Hi,**** > > ** ** > > I have a question regarding filters on timestamps. The syntax seems to be > UNIX_TIMESTAMP('yyyy-MM-dd hh:mm:ss'), is there another way to express a > datetime type? The problem is that I get an exception when using the IN > <list> syntax, while the equal comparison works without problems.**** > > ** ** > > Example: SELECT * FROM table1 WHERE datecol IN ( > UNIX_TIMESTAMP('2009-01-05 00:00:00'),UNIX_TIMESTAMP('2009-01-10 > 00:00:00') )**** > > ** ** > > Throws exception: ** ** > > ** ** > > Caused by: java.sql.SQLException: Query returned non-zero code: 10014, > cause: FAILED: SemanticException [Error 10014]: Line 5:21 Wrong arguments > ''2009-01-10 00:00:00'': The arguments for IN should be the same type! > Types are: {timestamp IN (bigint, bigint)}**** > > at > org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:189) > **** > > at > org.apache.hadoop.hive.jdbc.HiveStatement.execute(HiveStatement.java:127)* > *** > > at > com.sap.connectivity.cs.java.drivers.jdbc.JDBCAPI$Statement.execute(JDBCAPI.java:1648) > **** > > at > com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver.prepare(JDBCDriver.java:1760) > **** > > ... 15 more**** > > ** ** > > Following query works:**** > > ** ** > > SELECT * FROM table1 WHERE datecol = UNIX_TIMESTAMP('2009-01-17 > 00:00:00')**** > > ** ** > > Is there another syntax for datetime types? Could it be a bug in the > filter “IN <list>” operation?**** > > ** ** > > Thanks in advance,**** > > ** ** > > Steffen**** > > ** ** > -- Nitin Pawar