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.