java.sql.SQLException

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • Test fails with MS Access, I have not tested with any other DB. Error: testDrillThrough(mondrian.xmla.XmlaBasicTest)mondr ian.xmla.XmlaEx ception: Mondrian Error:XMLA Drill Through SQL error [java] at mondrian.xmla.XmlaHandler.executeDrillThroughQuery (XmlaHandle r.java:932) [java] at mondrian.xmla.XmlaHandler.execute(XmlaHandler.java :217) [java] at mondrian.xmla.XmlaHandler.process(XmlaHandler.java :135) [java] at mondrian.tui.XmlaSupport.processXmla(XmlaSupport.j ava:781) [java] at mondrian.tui.XmlaSupport.processXmla(XmlaSupport.j ava:764) [java] at mondrian.xmla.XmlaBasicTest.doTests(XmlaBasicTest. java:579) [java] at mondrian.xmla.XmlaBasicTest.doTestInline(XmlaBasic Test.java:5 26) [java] at mondrian.xmla.XmlaBasicTest.doTest(XmlaBasicTest.j ava:501) [java] at mondrian.xmla.XmlaBasicTest.testDrillThrough(XmlaB asicTest.ja va:414) [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method) [java] at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAcces sorImpl.java:39) [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMet hodAccessorImpl.java:25) [java] at mondrian.test.MondrianTestRunner$2.run(MondrianTes tRunner.jav a:130) [java] at java.lang.Thread.run(Thread.java:619) [java] Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error in drill through; sql=[select `store`.`store_country` as `Store Cou ntry`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store Ci ty`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time _by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `prod uct_class`.`product_family` as `Product Family`, `product_class`.`product_depart ment` as `Product Department`, `product_class`.`product_category` as `Product Ca tegory`, `product_class`.`product_subcategory` as `Product Subcategory`, `produc t`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `pr omotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotio n Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as ` City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `customer`.`education` as `Education Level`, `customer`.`gender` as `Gender`, `c ustomer`.`marital_status` as `Marital Status`, `customer`.`yearly_income` as `Ye arly Income`, `sales_fact_1997`.`unit_sales` as `Unit Sales` from `store` as `st ore`, `sales_fact_1997` as `sales_fact_1997`, `time_by_day` as `time_by_day`, `p roduct_class` as `product_class`, `product` as `product`, `promotion` as `promot ion`, `customer` as `customer` where `sales_fact_1997`.`store_id` = `store`.`sto re_id` and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and `time_by_da y`.`the_year` = 1997 and `sales_fact_1997`.`product_id` = `product`.`product_id` and `product`.`product_class_id` = `product_class`.`product_class_id` and `sale s_fact_1997`.`promotion_id` = `promotion`.`promotion_id` and `sales_fact_1997`.` customer_id` = `customer`.`customer_id` and `customer`.`state_province` = 'CA' a nd `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`. `store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.` store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_b y_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`produc t_family` ASC, `product_class`.`product_department` ASC, `product_class`.`produc t_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_nam e` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`. `promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fn ame + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_in come` ASC] [java] at mondrian.resource.MondrianResource$_Def0.ex(Mondri anResource. java:755) [java] at mondrian.olap.Util.newInternal(Util.java:1083) [java] at mondrian.olap.Util.newError(Util.java:1099) [java] at mondrian.rolap.SqlStatement.handle(SqlStatement.ja va:204) [java] at mondrian.rolap.SqlStatement.execute(SqlStatement.j ava:138) [java] at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.ja va:285) [java] at mondrian.xmla.XmlaHandler.executeDrillThroughQuery (XmlaHandle r.java:912) [java] ... 24 more [java] Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified express ion 'STORE COUNTRY' as part of an aggregate function. [java] at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc .java:6957) [java] at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java :7114) [java] at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java :3110) [java] at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcSt atement.jav a:338) [java] at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcO dbcStatemen t.java:253) [java] at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount( JdbcOdbcRes ultSet.java:6353) [java] at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdb cResultSet. java:155) [java] at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcO dbcStatemen t.java:424) [java] at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcO dbcStatemen t.java:254) [java] at org.apache.commons.dbcp.DelegatingStatement.execut eQuery(Dele gatingStatement.java:188) [java] at mondrian.rolap.SqlStatement.execute(SqlStatement.j ava:124) I suspect problem is with DBCP, Mondrian is firing only one query to DelegateStatement statement.executeQuery() // SqlStatement.java :126 But I could see in DriverManager log that two queries are being executed. Driver manager log: *Statement.executeQuery (select `store`.`store_country` as `Store Country`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store City`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time_by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `product_class`.`product_family` as `Product Family`, `product_class`.`product_department` as `Product Department`, `product_class`.`product_category` as `Product Category`, `product_class`.`product_subcategory` as `Product Subcategory`, `product`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `promotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotion Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as `City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `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_class` as `product_class`, `product` as `product`, `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`.`state_province` = 'CA' and `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`.`store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.`store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_by_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`product_family` ASC, `product_class`.`product_department` ASC, `product_class`.`product_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_name` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`.`promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fname + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_income` ASC) *Statement.execute (select `store`.`store_country` as `Store Country`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store City`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time_by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `product_class`.`product_family` as `Product Family`, `product_class`.`product_department` as `Product Department`, `product_class`.`product_category` as `Product Category`, `product_class`.`product_subcategory` as `Product Subcategory`, `product`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `promotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotion Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as `City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `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_class` as `product_class`, `product` as `product`, `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`.`state_province` = 'CA' and `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`.`store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.`store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_by_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`product_family` ASC, `product_class`.`product_department` ASC, `product_class`.`product_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_name` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`.`promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fname + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_income` ASC) Free statement (SQLFreeStmt), hStmt=189014408, fOption=0 Executing (SQLExecDirect), hStmt=189014408, szSqlStr=select `store`.`store_country` as `Store Country`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store City`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time_by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `product_class`.`product_family` as `Product Family`, `product_class`.`product_department` as `Product Department`, `product_class`.`product_category` as `Product Category`, `product_class`.`product_subcategory` as `Product Subcategory`, `product`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `promotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotion Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as `City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `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_class` as `product_class`, `product` as `product`, `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`.`state_province` = 'CA' and `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`.`store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.`store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_by_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`product_family` ASC, `product_class`.`product_department` ASC, `product_class`.`product_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_name` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`.`promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fname + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_income` ASC Number of result columns (SQLNumResultCols), hStmt=189014408 value=26 Get statement option (SQLGetStmtOption), hStmt=189014408, fOption=6 value=3 SQLWarning: reason(Result set type has been changed.) Number of result columns (SQLNumResultCols), hStmt=189014408 value=26 Setting statement option (SQLSetStmtAttr), hStmt=189014408, fOption=25 Get Statement Attribute (SQLGetStmtAttr), hDbc=189014408, AttrType=6 int value=3 Get connection info (SQLGetInfo), hDbc=189018760, fInfoType=168 int value=1 Number of affected rows (SQLRowCount), hStmt=189014408 value=-1 Fetching (SQLFetchScroll), hStmt=189014408 Get statement option (SQLGetStmtOption), hStmt=189014408, fOption=14 value=0 Fetching (SQLFetchScroll), hStmt=189014408 End of result set (SQL_NO_DATA) *Connection.createStatement Allocating Statement Handle (SQLAllocStmt), hDbc=189018760 hStmt=189019312 Setting statement option (SQLSetStmtOption), hStmt=189019312, fOption=6, vParam=0 Setting statement option (SQLSetStmtOption), hStmt=189019312, fOption=7, vParam=1 Registering Statement sun.jdbc.odbc.JdbcOdbcStatement@3c9616 *Statement.executeQuery (SELECT COUNT(*), `STORE`.`STORE_COUNTRY` AS `STORE COUNTRY`, `STORE`.`STORE_STATE` AS `STORE STATE`, `STORE`.`STORE_CITY` AS `STORE CITY`, `STORE`.`STORE_NAME` AS `STORE NAME`, `STORE`.`STORE_SQFT` AS `STORE SQFT`, `STORE`.`STORE_TYPE` AS `STORE TYPE`, `TIME_BY_DAY`.`THE_YEAR` AS `YEAR`, `TIME_BY_DAY`.`QUARTER` AS `QUARTER`, `TIME_BY_DAY`.`MONTH_OF_YEAR` AS `MONTH`, `PRODUCT_CLASS`.`PRODUCT_FAMILY` AS `PRODUCT FAMILY`, `PRODUCT_CLASS`.`PRODUCT_DEPARTMENT` AS `PRODUCT DEPARTMENT`, `PRODUCT_CLASS`.`PRODUCT_CATEGORY` AS `PRODUCT CATEGORY`, `PRODUCT_CLASS`.`PRODUCT_SUBCATEGORY` AS `PRODUCT SUBCATEGORY`, `PRODUCT`.`BRAND_NAME` AS `BRAND NAME`, `PRODUCT`.`PRODUCT_NAME` AS `PRODUCT NAME`, `PROMOTION`.`MEDIA_TYPE` AS `MEDIA TYPE`, `PROMOTION`.`PROMOTION_NAME` AS `PROMOTION NAME`, `CUSTOMER`.`STATE_PROVINCE` AS `STATE PROVINCE`, `CUSTOMER`.`CITY` AS `CITY`, FNAME + ' ' + LNAME AS `NAME`, `CUSTOMER`.`CUSTOMER_ID` AS `NAME (KEY)`, `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_CLASS` AS `PRODUCT_CLASS`, `PRODUCT` AS `PRODUCT`, `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`.`STATE_PROVINCE` = 'CA' AND `CUSTOMER`.`CITY` = 'BERKELEY' ) *Statement.execute (SELECT COUNT(*), `STORE`.`STORE_COUNTRY` AS `STORE COUNTRY`, `STORE`.`STORE_STATE` AS `STORE STATE`, `STORE`.`STORE_CITY` AS `STORE CITY`, `STORE`.`STORE_NAME` AS `STORE NAME`, `STORE`.`STORE_SQFT` AS `STORE SQFT`, `STORE`.`STORE_TYPE` AS `STORE TYPE`, `TIME_BY_DAY`.`THE_YEAR` AS `YEAR`, `TIME_BY_DAY`.`QUARTER` AS `QUARTER`, `TIME_BY_DAY`.`MONTH_OF_YEAR` AS `MONTH`, `PRODUCT_CLASS`.`PRODUCT_FAMILY` AS `PRODUCT FAMILY`, `PRODUCT_CLASS`.`PRODUCT_DEPARTMENT` AS `PRODUCT DEPARTMENT`, `PRODUCT_CLASS`.`PRODUCT_CATEGORY` AS `PRODUCT CATEGORY`, `PRODUCT_CLASS`.`PRODUCT_SUBCATEGORY` AS `PRODUCT SUBCATEGORY`, `PRODUCT`.`BRAND_NAME` AS `BRAND NAME`, `PRODUCT`.`PRODUCT_NAME` AS `PRODUCT NAME`, `PROMOTION`.`MEDIA_TYPE` AS `MEDIA TYPE`, `PROMOTION`.`PROMOTION_NAME` AS `PROMOTION NAME`, `CUSTOMER`.`STATE_PROVINCE` AS `STATE PROVINCE`, `CUSTOMER`.`CITY` AS `CITY`, FNAME + ' ' + LNAME AS `NAME`, `CUSTOMER`.`CUSTOMER_ID` AS `NAME (KEY)`, `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_CLASS` AS `PRODUCT_CLASS`, `PRODUCT` AS `PRODUCT`, `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`.`STATE_PROVINCE` = 'CA' AND `CUSTOMER`.`CITY` = 'BERKELEY' ) Free statement (SQLFreeStmt), hStmt=189019312, fOption=0 Executing (SQLExecDirect), hStmt=189019312, szSqlStr=SELECT COUNT(*), `STORE`.`STORE_COUNTRY` AS `STORE COUNTRY`, `STORE`.`STORE_STATE` AS `STORE STATE`, `STORE`.`STORE_CITY` AS `STORE CITY`, `STORE`.`STORE_NAME` AS `STORE NAME`, `STORE`.`STORE_SQFT` AS `STORE SQFT`, `STORE`.`STORE_TYPE` AS `STORE TYPE`, `TIME_BY_DAY`.`THE_YEAR` AS `YEAR`, `TIME_BY_DAY`.`QUARTER` AS `QUARTER`, `TIME_BY_DAY`.`MONTH_OF_YEAR` AS `MONTH`, `PRODUCT_CLASS`.`PRODUCT_FAMILY` AS `PRODUCT FAMILY`, `PRODUCT_CLASS`.`PRODUCT_DEPARTMENT` AS `PRODUCT DEPARTMENT`, `PRODUCT_CLASS`.`PRODUCT_CATEGORY` AS `PRODUCT CATEGORY`, `PRODUCT_CLASS`.`PRODUCT_SUBCATEGORY` AS `PRODUCT SUBCATEGORY`, `PRODUCT`.`BRAND_NAME` AS `BRAND NAME`, `PRODUCT`.`PRODUCT_NAME` AS `PRODUCT NAME`, `PROMOTION`.`MEDIA_TYPE` AS `MEDIA TYPE`, `PROMOTION`.`PROMOTION_NAME` AS `PROMOTION NAME`, `CUSTOMER`.`STATE_PROVINCE` AS `STATE PROVINCE`, `CUSTOMER`.`CITY` AS `CITY`, FNAME + ' ' + LNAME AS `NAME`, `CUSTOMER`.`CUSTOMER_ID` AS `NAME (KEY)`, `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_CLASS` AS `PRODUCT_CLASS`, `PRODUCT` AS `PRODUCT`, `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`.`STATE_PROVINCE` = 'CA' AND `CUSTOMER`.`CITY` = 'BERKELEY' RETCODE = -1 ERROR - Generating SQLException... SQLException: SQLState(37000) vendor code(-3011) java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'STORE COUNTRY' as part of an aggregate function. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111) ResultSet.finalize sun.jdbc.odbc.JdbcOdbcResultSet@1a18ee2 at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:253) at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6354) at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:154) Statement.finalize sun.jdbc.odbc.JdbcOdbcStatement@811e10 at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:424) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:254) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:188) *Statement.close at mondrian.rolap.SqlStatement.execute(SqlStatement.java:126) at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:285) ResultSet.finalize sun.jdbc.odbc.JdbcOdbcResultSet@1abcd9b
    via by p_thiyagu,
  • Test fails with MS Access, I have not tested with any other DB. Error: testDrillThrough(mondrian.xmla.XmlaBasicTest)mondr ian.xmla.XmlaEx ception: Mondrian Error:XMLA Drill Through SQL error [java] at mondrian.xmla.XmlaHandler.executeDrillThroughQuery (XmlaHandle r.java:932) [java] at mondrian.xmla.XmlaHandler.execute(XmlaHandler.java :217) [java] at mondrian.xmla.XmlaHandler.process(XmlaHandler.java :135) [java] at mondrian.tui.XmlaSupport.processXmla(XmlaSupport.j ava:781) [java] at mondrian.tui.XmlaSupport.processXmla(XmlaSupport.j ava:764) [java] at mondrian.xmla.XmlaBasicTest.doTests(XmlaBasicTest. java:579) [java] at mondrian.xmla.XmlaBasicTest.doTestInline(XmlaBasic Test.java:5 26) [java] at mondrian.xmla.XmlaBasicTest.doTest(XmlaBasicTest.j ava:501) [java] at mondrian.xmla.XmlaBasicTest.testDrillThrough(XmlaB asicTest.ja va:414) [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method) [java] at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAcces sorImpl.java:39) [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMet hodAccessorImpl.java:25) [java] at mondrian.test.MondrianTestRunner$2.run(MondrianTes tRunner.jav a:130) [java] at java.lang.Thread.run(Thread.java:619) [java] Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error in drill through; sql=[select `store`.`store_country` as `Store Cou ntry`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store Ci ty`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time _by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `prod uct_class`.`product_family` as `Product Family`, `product_class`.`product_depart ment` as `Product Department`, `product_class`.`product_category` as `Product Ca tegory`, `product_class`.`product_subcategory` as `Product Subcategory`, `produc t`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `pr omotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotio n Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as ` City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `customer`.`education` as `Education Level`, `customer`.`gender` as `Gender`, `c ustomer`.`marital_status` as `Marital Status`, `customer`.`yearly_income` as `Ye arly Income`, `sales_fact_1997`.`unit_sales` as `Unit Sales` from `store` as `st ore`, `sales_fact_1997` as `sales_fact_1997`, `time_by_day` as `time_by_day`, `p roduct_class` as `product_class`, `product` as `product`, `promotion` as `promot ion`, `customer` as `customer` where `sales_fact_1997`.`store_id` = `store`.`sto re_id` and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and `time_by_da y`.`the_year` = 1997 and `sales_fact_1997`.`product_id` = `product`.`product_id` and `product`.`product_class_id` = `product_class`.`product_class_id` and `sale s_fact_1997`.`promotion_id` = `promotion`.`promotion_id` and `sales_fact_1997`.` customer_id` = `customer`.`customer_id` and `customer`.`state_province` = 'CA' a nd `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`. `store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.` store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_b y_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`produc t_family` ASC, `product_class`.`product_department` ASC, `product_class`.`produc t_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_nam e` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`. `promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fn ame + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_in come` ASC] [java] at mondrian.resource.MondrianResource$_Def0.ex(Mondri anResource. java:755) [java] at mondrian.olap.Util.newInternal(Util.java:1083) [java] at mondrian.olap.Util.newError(Util.java:1099) [java] at mondrian.rolap.SqlStatement.handle(SqlStatement.ja va:204) [java] at mondrian.rolap.SqlStatement.execute(SqlStatement.j ava:138) [java] at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.ja va:285) [java] at mondrian.xmla.XmlaHandler.executeDrillThroughQuery (XmlaHandle r.java:912) [java] ... 24 more [java] Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified express ion 'STORE COUNTRY' as part of an aggregate function. [java] at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc .java:6957) [java] at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java :7114) [java] at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java :3110) [java] at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcSt atement.jav a:338) [java] at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcO dbcStatemen t.java:253) [java] at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount( JdbcOdbcRes ultSet.java:6353) [java] at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdb cResultSet. java:155) [java] at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcO dbcStatemen t.java:424) [java] at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcO dbcStatemen t.java:254) [java] at org.apache.commons.dbcp.DelegatingStatement.execut eQuery(Dele gatingStatement.java:188) [java] at mondrian.rolap.SqlStatement.execute(SqlStatement.j ava:124) I suspect problem is with DBCP, Mondrian is firing only one query to DelegateStatement statement.executeQuery() // SqlStatement.java :126 But I could see in DriverManager log that two queries are being executed. Driver manager log: *Statement.executeQuery (select `store`.`store_country` as `Store Country`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store City`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time_by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `product_class`.`product_family` as `Product Family`, `product_class`.`product_department` as `Product Department`, `product_class`.`product_category` as `Product Category`, `product_class`.`product_subcategory` as `Product Subcategory`, `product`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `promotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotion Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as `City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `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_class` as `product_class`, `product` as `product`, `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`.`state_province` = 'CA' and `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`.`store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.`store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_by_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`product_family` ASC, `product_class`.`product_department` ASC, `product_class`.`product_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_name` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`.`promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fname + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_income` ASC) *Statement.execute (select `store`.`store_country` as `Store Country`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store City`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time_by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `product_class`.`product_family` as `Product Family`, `product_class`.`product_department` as `Product Department`, `product_class`.`product_category` as `Product Category`, `product_class`.`product_subcategory` as `Product Subcategory`, `product`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `promotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotion Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as `City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `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_class` as `product_class`, `product` as `product`, `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`.`state_province` = 'CA' and `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`.`store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.`store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_by_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`product_family` ASC, `product_class`.`product_department` ASC, `product_class`.`product_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_name` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`.`promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fname + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_income` ASC) Free statement (SQLFreeStmt), hStmt=189014408, fOption=0 Executing (SQLExecDirect), hStmt=189014408, szSqlStr=select `store`.`store_country` as `Store Country`, `store`.`store_state` as `Store State`, `store`.`store_city` as `Store City`, `store`.`store_name` as `Store Name`, `store`.`store_sqft` as `Store Sqft`, `store`.`store_type` as `Store Type`, `time_by_day`.`the_year` as `Year`, `time_by_day`.`quarter` as `Quarter`, `time_by_day`.`month_of_year` as `Month`, `product_class`.`product_family` as `Product Family`, `product_class`.`product_department` as `Product Department`, `product_class`.`product_category` as `Product Category`, `product_class`.`product_subcategory` as `Product Subcategory`, `product`.`brand_name` as `Brand Name`, `product`.`product_name` as `Product Name`, `promotion`.`media_type` as `Media Type`, `promotion`.`promotion_name` as `Promotion Name`, `customer`.`state_province` as `State Province`, `customer`.`city` as `City`, fname + ' ' + lname as `Name`, `customer`.`customer_id` as `Name (Key)`, `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_class` as `product_class`, `product` as `product`, `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`.`state_province` = 'CA' and `customer`.`city` = 'Berkeley' order by `store`.`store_country` ASC, `store`.`store_state` ASC, `store`.`store_city` ASC, `store`.`store_name` ASC, `store`.`store_sqft` ASC, `store`.`store_type` ASC, `time_by_day`.`the_year` ASC, `time_by_day`.`quarter` ASC, `time_by_day`.`month_of_year` ASC, `product_class`.`product_family` ASC, `product_class`.`product_department` ASC, `product_class`.`product_category` ASC, `product_class`.`product_subcategory` ASC, `product`.`brand_name` ASC, `product`.`product_name` ASC, `promotion`.`media_type` ASC, `promotion`.`promotion_name` ASC, `customer`.`state_province` ASC, `customer`.`city` ASC, fname + ' ' + lname ASC, `customer`.`customer_id` ASC, `customer`.`education` ASC, `customer`.`gender` ASC, `customer`.`marital_status` ASC, `customer`.`yearly_income` ASC Number of result columns (SQLNumResultCols), hStmt=189014408 value=26 Get statement option (SQLGetStmtOption), hStmt=189014408, fOption=6 value=3 SQLWarning: reason(Result set type has been changed.) Number of result columns (SQLNumResultCols), hStmt=189014408 value=26 Setting statement option (SQLSetStmtAttr), hStmt=189014408, fOption=25 Get Statement Attribute (SQLGetStmtAttr), hDbc=189014408, AttrType=6 int value=3 Get connection info (SQLGetInfo), hDbc=189018760, fInfoType=168 int value=1 Number of affected rows (SQLRowCount), hStmt=189014408 value=-1 Fetching (SQLFetchScroll), hStmt=189014408 Get statement option (SQLGetStmtOption), hStmt=189014408, fOption=14 value=0 Fetching (SQLFetchScroll), hStmt=189014408 End of result set (SQL_NO_DATA) *Connection.createStatement Allocating Statement Handle (SQLAllocStmt), hDbc=189018760 hStmt=189019312 Setting statement option (SQLSetStmtOption), hStmt=189019312, fOption=6, vParam=0 Setting statement option (SQLSetStmtOption), hStmt=189019312, fOption=7, vParam=1 Registering Statement sun.jdbc.odbc.JdbcOdbcStatement@3c9616 *Statement.executeQuery (SELECT COUNT(*), `STORE`.`STORE_COUNTRY` AS `STORE COUNTRY`, `STORE`.`STORE_STATE` AS `STORE STATE`, `STORE`.`STORE_CITY` AS `STORE CITY`, `STORE`.`STORE_NAME` AS `STORE NAME`, `STORE`.`STORE_SQFT` AS `STORE SQFT`, `STORE`.`STORE_TYPE` AS `STORE TYPE`, `TIME_BY_DAY`.`THE_YEAR` AS `YEAR`, `TIME_BY_DAY`.`QUARTER` AS `QUARTER`, `TIME_BY_DAY`.`MONTH_OF_YEAR` AS `MONTH`, `PRODUCT_CLASS`.`PRODUCT_FAMILY` AS `PRODUCT FAMILY`, `PRODUCT_CLASS`.`PRODUCT_DEPARTMENT` AS `PRODUCT DEPARTMENT`, `PRODUCT_CLASS`.`PRODUCT_CATEGORY` AS `PRODUCT CATEGORY`, `PRODUCT_CLASS`.`PRODUCT_SUBCATEGORY` AS `PRODUCT SUBCATEGORY`, `PRODUCT`.`BRAND_NAME` AS `BRAND NAME`, `PRODUCT`.`PRODUCT_NAME` AS `PRODUCT NAME`, `PROMOTION`.`MEDIA_TYPE` AS `MEDIA TYPE`, `PROMOTION`.`PROMOTION_NAME` AS `PROMOTION NAME`, `CUSTOMER`.`STATE_PROVINCE` AS `STATE PROVINCE`, `CUSTOMER`.`CITY` AS `CITY`, FNAME + ' ' + LNAME AS `NAME`, `CUSTOMER`.`CUSTOMER_ID` AS `NAME (KEY)`, `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_CLASS` AS `PRODUCT_CLASS`, `PRODUCT` AS `PRODUCT`, `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`.`STATE_PROVINCE` = 'CA' AND `CUSTOMER`.`CITY` = 'BERKELEY' ) *Statement.execute (SELECT COUNT(*), `STORE`.`STORE_COUNTRY` AS `STORE COUNTRY`, `STORE`.`STORE_STATE` AS `STORE STATE`, `STORE`.`STORE_CITY` AS `STORE CITY`, `STORE`.`STORE_NAME` AS `STORE NAME`, `STORE`.`STORE_SQFT` AS `STORE SQFT`, `STORE`.`STORE_TYPE` AS `STORE TYPE`, `TIME_BY_DAY`.`THE_YEAR` AS `YEAR`, `TIME_BY_DAY`.`QUARTER` AS `QUARTER`, `TIME_BY_DAY`.`MONTH_OF_YEAR` AS `MONTH`, `PRODUCT_CLASS`.`PRODUCT_FAMILY` AS `PRODUCT FAMILY`, `PRODUCT_CLASS`.`PRODUCT_DEPARTMENT` AS `PRODUCT DEPARTMENT`, `PRODUCT_CLASS`.`PRODUCT_CATEGORY` AS `PRODUCT CATEGORY`, `PRODUCT_CLASS`.`PRODUCT_SUBCATEGORY` AS `PRODUCT SUBCATEGORY`, `PRODUCT`.`BRAND_NAME` AS `BRAND NAME`, `PRODUCT`.`PRODUCT_NAME` AS `PRODUCT NAME`, `PROMOTION`.`MEDIA_TYPE` AS `MEDIA TYPE`, `PROMOTION`.`PROMOTION_NAME` AS `PROMOTION NAME`, `CUSTOMER`.`STATE_PROVINCE` AS `STATE PROVINCE`, `CUSTOMER`.`CITY` AS `CITY`, FNAME + ' ' + LNAME AS `NAME`, `CUSTOMER`.`CUSTOMER_ID` AS `NAME (KEY)`, `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_CLASS` AS `PRODUCT_CLASS`, `PRODUCT` AS `PRODUCT`, `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`.`STATE_PROVINCE` = 'CA' AND `CUSTOMER`.`CITY` = 'BERKELEY' ) Free statement (SQLFreeStmt), hStmt=189019312, fOption=0 Executing (SQLExecDirect), hStmt=189019312, szSqlStr=SELECT COUNT(*), `STORE`.`STORE_COUNTRY` AS `STORE COUNTRY`, `STORE`.`STORE_STATE` AS `STORE STATE`, `STORE`.`STORE_CITY` AS `STORE CITY`, `STORE`.`STORE_NAME` AS `STORE NAME`, `STORE`.`STORE_SQFT` AS `STORE SQFT`, `STORE`.`STORE_TYPE` AS `STORE TYPE`, `TIME_BY_DAY`.`THE_YEAR` AS `YEAR`, `TIME_BY_DAY`.`QUARTER` AS `QUARTER`, `TIME_BY_DAY`.`MONTH_OF_YEAR` AS `MONTH`, `PRODUCT_CLASS`.`PRODUCT_FAMILY` AS `PRODUCT FAMILY`, `PRODUCT_CLASS`.`PRODUCT_DEPARTMENT` AS `PRODUCT DEPARTMENT`, `PRODUCT_CLASS`.`PRODUCT_CATEGORY` AS `PRODUCT CATEGORY`, `PRODUCT_CLASS`.`PRODUCT_SUBCATEGORY` AS `PRODUCT SUBCATEGORY`, `PRODUCT`.`BRAND_NAME` AS `BRAND NAME`, `PRODUCT`.`PRODUCT_NAME` AS `PRODUCT NAME`, `PROMOTION`.`MEDIA_TYPE` AS `MEDIA TYPE`, `PROMOTION`.`PROMOTION_NAME` AS `PROMOTION NAME`, `CUSTOMER`.`STATE_PROVINCE` AS `STATE PROVINCE`, `CUSTOMER`.`CITY` AS `CITY`, FNAME + ' ' + LNAME AS `NAME`, `CUSTOMER`.`CUSTOMER_ID` AS `NAME (KEY)`, `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_CLASS` AS `PRODUCT_CLASS`, `PRODUCT` AS `PRODUCT`, `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`.`STATE_PROVINCE` = 'CA' AND `CUSTOMER`.`CITY` = 'BERKELEY' RETCODE = -1 ERROR - Generating SQLException... SQLException: SQLState(37000) vendor code(-3011) java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'STORE COUNTRY' as part of an aggregate function. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111) ResultSet.finalize sun.jdbc.odbc.JdbcOdbcResultSet@1a18ee2 at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:253) at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6354) at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:154) Statement.finalize sun.jdbc.odbc.JdbcOdbcStatement@811e10 at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:424) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:254) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:188) *Statement.close at mondrian.rolap.SqlStatement.execute(SqlStatement.java:126) at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:285) ResultSet.finalize sun.jdbc.odbc.JdbcOdbcResultSet@1abcd9b
    via by p_thiyagu,
  • Problem using JDBC and MS SQL Server
    via by 843854,
  • Truncate table using java code
    via Stack Overflow by ManojB
    ,
  • Weird java.sql.SQLException error
    via Stack Overflow by Bamizas
    ,
  • Java-Database (oracle) odbc driver mismatch
    via Stack Overflow by RC-user
    ,
  • access 2010 connection with java
    via Stack Overflow by Arifah Azhar
    ,
    • java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'STORE COUNTRY' as part of an aggregate function. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)

    Users with the same issue

    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor2 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    12 more bugmates