Reserved Words"INTERVAL" in SAP HANA SQL

[naomy_xu]

Hi,

The query counts the number of orders ordered in a given quarter of a given year in which at least one lineitem was received by the customer later than its committed date. And i used 'interval' to get the quarter.But the query can not be executed.How can I fix this problem?

select

  o_orderpriority,

  count(*) as order_count

from

  orders

where

  o_orderdate >= date '1993-07-01'

  and o_orderdate < date '1993-07-01' + interval '3' month

  and exists (

  select

  *

  from

  lineitem

  where

  l_orderkey = o_orderkey

  and l_commitdate < l_receiptdate

  )

Thanks in advance.

Naomy

[Former Member · Accepted solution]

Hello Naomy,

I am not familiar with this kind of syntax and would usually either explicitly specify the ranges or use a function like ADD_DAYS:

  o_orderdate between to_date('1993-07-01', 'yyyy-mm-dd')

  and to_date('1993-09-30, 'yyyy-mm-dd')

  o_orderdate between to_date('1993-07-01', 'yyyy-mm-dd')

  and add_days(to_date('1993-07-01', 'yyyy-mm-dd'), 91)

Alternatively you can use the QUARTER function, see QUARTER - SAP HANA SQL and System Views Reference - SAP Library.

Kind regards

Martin

[naomy_xu · Accepted solution]

Thank you very much,Martin.

I specified the ranges and use the ADD_DAYS function, and it work.