Forum: Other

Post: How create view

How create view
Debora
Aug 29, 2013
Hi,

I'm trying create this view:

create view revenue0 (supplier_no, total_revenue, total) as
select
l_suppkey as supplier_no,
sum(l_extendedprice * (1 - l_discount)) as total_revenue
count(*) as total
from
lineitem
where
l_shipdate >= '1994-03-01'
and l_shipdate < '1994-06-01'
group by
l_suppkey;

but I get this error: Unexpected Ad Hoc Planning Error: java.lang.RuntimeException: Error compiling query: org.voltdb.planner.PlanningErrorException: unexpected end of statement

And I don't know what I'm doing wrong. Can anyone help me?

Thanks,
Débora
pmartel
Aug 29, 2013
VoltDB supports a limited view definition syntax as described here:

http://voltdb.com/docs/UsingVoltDB/ddlref_createview.php

Your view definition contains the following elements that are currently unsupported:
count(*) listed after aggregates

Also, currently missing from the documentation is a restriction that the aggregates can only be sums or counts of simple column values, not arbitrary expressions.
The workaround for this example would be to store an additional (l_revenue) column on lineitem that was maintained so that l_revenue = l_extendedprice * (1 - l_discount).
Support for aggregates of arbitrary expressions in view definitions is in development for an upcoming release.

Clearly, the given error message is not helpful -- there MAY be some other issue with the query that is confusing the diagnostic system -- I'll raise that as an open issue for the development team.

We apologize for any inconvenience.

Paul Martel
VoltDB Engineering