db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde" <julianh...@speakeasy.net>
Subject Unexpected error "When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions."
Date Mon, 13 Nov 2006 20:06:24 GMT
The query

select "store"."store_name" as "Store Name",
    "store"."store_city" as "Store City",
    "store"."store_state" as "Store State",
    "store"."store_country" as "Store Country",
    "store"."store_sqft" as "Store Sqft",
    "store"."store_type" as "Store Type",
    "time_by_day"."month_of_year" as "Month",
    "time_by_day"."quarter" as "Quarter",
    "time_by_day"."the_year" as "Year",
    "product"."product_name" as "Product Name",
    "product"."brand_name" as "Brand Name",
    "product_class"."product_subcategory" as "Product Subcategory",
    "product_class"."product_category" as "Product Category",
    "product_class"."product_department" as "Product Department",
    "product_class"."product_family" as "Product Family",
    "promotion"."media_type" as "Media Type",
    "promotion"."promotion_name" as "Promotion Name",
    "customer"."customer_id" as "Name (Key)",
    "customer"."fullname" as "Name",
    "customer"."city" as "City",
    "customer"."state_province" as "State Province",
    "customer"."education" as "Education Level",
    "customer"."gender" as "Gender",
    "customer"."marital_status" as "Marital Status",
    "customer"."yearly_income" as "Yearly Income",
    "sales_fact_1997"."unit_sales" as "Unit Sales"
from "store" as "store",
    "sales_fact_1997" as "sales_fact_1997",
    "time_by_day" as "time_by_day",
    "product" as "product",
    "product_class" as "product_class",
    "promotion" as "promotion",
    "customer" as "customer"
where "sales_fact_1997"."store_id" = "store"."store_id"
and "sales_fact_1997"."time_id" = "time_by_day"."time_id"
and "time_by_day"."the_year" = 1997
and "sales_fact_1997"."product_id" = "product"."product_id"
and "product"."product_class_id" = "product_class"."product_class_id"
and "sales_fact_1997"."promotion_id" = "promotion"."promotion_id"
and "sales_fact_1997"."customer_id" = "customer"."customer_id"
and "customer"."city" = 'Berkeley'
and "customer"."state_province" = 'CA'
order by "store"."store_name" ASC,
    "store"."store_city" ASC,
    "store"."store_state" ASC,
    "store"."store_country" ASC,
    "store"."store_sqft" ASC,
    "store"."store_type" ASC,
    "time_by_day"."month_of_year" ASC,
    "time_by_day"."quarter" ASC,
    "time_by_day"."the_year" ASC,
    "product"."product_name" ASC,
    "product"."brand_name" ASC,
    "product_class"."product_subcategory" ASC,
    "product_class"."product_category" ASC,
    "product_class"."product_department" ASC,
    "product_class"."product_family" ASC,
    "promotion"."media_type" ASC,
    "promotion"."promotion_name" ASC,
    "customer"."customer_id" ASC,
    "customer"."fullname" ASC,
    "customer"."city" ASC,
    "customer"."state_province" ASC,
    "customer"."education" ASC,
    "customer"."gender" ASC,
    "customer"."marital_status" ASC,
    "customer"."yearly_income" ASC

gives the error

SQL Exception: Column reference 'store.store_name' is invalid. When the
SELECT list contains at least one aggregate then all entries must be valid
aggregate expressions.

But the query does not contain any aggregate functions and, even curioser,
it succeeds if I remove the ORDER BY clause.

Is this a bug in derby? Is there a workaround? I'm running Derby 10.1.2.1 on
Fedora Core 5.

FYI, the query is generated by mondrian-2.2 to drill through to the set of
fact table rows underlying a particular cell in a multidimensional result.
If there is an equivalent query which doesn't encounter this bug, let me
know, and I can change mondrian's query generator.

Julian


Mime
View raw message