Forum Stats

  • 3,838,834 Users
  • 2,262,404 Discussions
  • 7,900,766 Comments

Discussions

Date conversion in CQL

2664040
2664040 Member Posts: 10
edited Sep 22, 2014 9:48PM in Complex Event Processing

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.

Best Answer

  • 2664040
    2664040 Member Posts: 10
    Answer ✓

    Anyway, I found a tricky answer somehow. Using to_timestamp function we can convert string to a time stamp and then apply required conditions.

    So, the line

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

    should look like:

    to_timestamp(SHIPDATE || " 00:00:00") <=  to_timestamp("12/01/1998 00:00:00")

    Obviously, the SHIPDATE (in my case) doesn't have the expected format, so a trivial concatenation is required. Moreover, Interval construct in CQL seems limited to INTERVAL DAY TO SECOND, so I am going to apply the required calculation for this manually.

Answers

  • 2664040
    2664040 Member Posts: 10
    Answer ✓

    Anyway, I found a tricky answer somehow. Using to_timestamp function we can convert string to a time stamp and then apply required conditions.

    So, the line

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

    should look like:

    to_timestamp(SHIPDATE || " 00:00:00") <=  to_timestamp("12/01/1998 00:00:00")

    Obviously, the SHIPDATE (in my case) doesn't have the expected format, so a trivial concatenation is required. Moreover, Interval construct in CQL seems limited to INTERVAL DAY TO SECOND, so I am going to apply the required calculation for this manually.

This discussion has been closed.