|
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
|
