What does orderdate look like? Maybe just select that alone. It looks like the cast to date is failing for orderstatus as the input string is not recognized correctly. Perhaps do cast varchar and then date.
--Andries > On Feb 12, 2015, at 7:42 PM, Sudhakar Thota <[email protected]> wrote: > > Hi Drillers, > > I am hitting a strange problem with TPC-H orders MapR-DB table while querying > using drill. It brings some records and results in errors showing "Value 0 > for monthOfYear”. I have verified the data in tsv file none of the record has > 0 month. > > And the query is this. > > 0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> select cast(row_key as > varchar(20)) as order_id, cast(t.CF1.`clerk` as varchar(20)) as order_clerk, > cast(t.CF1.orderstatus as varchar(20)) as ord_stat,cast(t.CF1.orderdate as > date) ordrdt from m7thotas.orders t where cast(t.CF1.orderdate as date) > BETWEEN cast('1996-01-10' as date) AND cast('1996-01-11' as date) and > cast(t.CF1.`clerk` as varchar(20))<>cast('Clerk#000000334' as varchar(20)); > > | 3421989 | Clerk#000000601 | O | 1996-01-10 | > | 3422915 | Clerk#000000058 | O | 1996-01-11 | > | 3423106 | Clerk#000000266 | O | 1996-01-11 | > Query failed: Query failed: Failure while running fragment., Value 0 for > monthOfYear must be in the range [1,12] [ > e117d519-b989-4e95-8b7c-d9cb2ab68084 on se-node11.se.lab:31010 ] > [ e117d519-b989-4e95-8b7c-d9cb2ab68084 on se-node11.se.lab:31010 ] > > > java.lang.RuntimeException: java.sql.SQLException: Failure while executing > query. > at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2514) > at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2148) > at sqlline.SqlLine.print(SqlLine.java:1809) > at sqlline.SqlLine$Commands.execute(SqlLine.java:3766) > at sqlline.SqlLine$Commands.sql(SqlLine.java:3663) > at sqlline.SqlLine.dispatch(SqlLine.java:889) > at sqlline.SqlLine.begin(SqlLine.java:763) > at sqlline.SqlLine.start(SqlLine.java:498) > at sqlline.SqlLine.main(SqlLine.java:460) > > > Please let me know if there is any thing that I am doing wrong and if there > is any work around. > > Thanks > Sudhakar Thota > > >
