Skip to Main Content

Date conversion in CQL

2664040Sep 19 2014 — edited Sep 22 2014

I want to run the following query from TPC-H in Oracle CEP.

SELECT RETURNFLAG,LINESTATUS,SUM(QUANTITY) AS sum_qty,SUM(EXTENDEDPRICE) AS  sum_base_price,

             SUM(EXTENDEDPRICE*(1-DISCOUNT)) AS sum_disc_price, SUM(EXTENDEDPRICE*(1-DISCOUNT)*(1+TAX)) AS sum_charge,

             AVG(QUANTITY) AS avg_qty, AVG(EXTENDEDPRICE) AS avg_price, AVG(DISCOUNT) AS avg_disc, COUNT(*) AS count_order

              FROM AdapterOutputChannel

              WHERE

              SHIPDATE <= date  '1998-12-01' - interval ':1' day (3)

              GROUP BY

              RETURNFLAG,LINESTATUS

              ORDER BY

              RETURNFLAG, LINESTATUS

I got a problem:

How should I rewrite the bold face line to make it executable for CEP engine? In current form it gives me error.

BTW, I have tried SHIPDATE <= '1998-12-01', which can be executed but  based on the result it seems the condition doesn't work correctly. I am using csvgen so that I bound to some limited datatypes. Thus, SHIPDATE  is string, however the cql doc says the conversion from string to date is done implicitly.

Thanks.

Comments
Post Details
Added on Sep 19 2014
1 comment
781 views