java.sql.SQLException: Could not execute the query

Oracle Community | 807594 | 1 decade ago
  1. 0

    SQL Problem?

    Oracle Community | 1 decade ago | 807594
    java.sql.SQLException: Could not execute the query
  2. 0

    Maze solver method

    Coderanch | 4 years ago | Wendy Gibbons
    java.sql.SQLException: Could not set SQL query
  3. 0

    Here is the simple query created with the Query Designer which fails to execute. {code} SELECT "Kettle"."344DataService"."PRODUCTLINE", sum("Kettle"."344DataService"."SALES") FROM "Kettle"."344DataService" GROUP BY "344DataService"."PRODUCTLINE" {code} {noformat} org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT "Kettle"."344DataService"."PRODUCTLINE", sum(Kettle"."344DataService"."SALES") FROM "Kettle"."344DataService" GROUP BY "344DataService"."PRODUCTLINE" at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:183) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:139) at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:95) at java.lang.Thread.run(Thread.java:722) Caused by: java.sql.SQLException: Unable to execute query: at org.pentaho.di.trans.dataservice.jdbc.ThinStatement.executeQuery(ThinStatement.java:123) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:308) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:178) ... 3 more Caused by: java.sql.SQLException: Unable to get open query for SQL: SELECT "Kettle"."344DataService"."PRODUCTLINE", sum(Kettle"."344DataService"."SALES") FROM "Kettle"."344DataService" GROUP BY "344DataService"."PRODUCTLINE" org.pentaho.di.core.exception.KettleException: No closing " found, starting at location 175 in : [SELECT "KETTLE"."344DATASERVICE"."PRODUCTLINE", SUM(KETTLE"."344DATASERVICE"."SALES") FROM "KETTLE"."344DATASERVICE" GROUP BY "344DATASERVICE"."PRODUCTLINE"] at org.pentaho.di.cluster.HttpUtil.execMethod(HttpUtil.java:138) at org.pentaho.di.cluster.HttpUtil.execService(HttpUtil.java:95) at org.pentaho.di.trans.dataservice.jdbc.ThinResultSet.remoteQuery(ThinResultSet.java:144) at org.pentaho.di.trans.dataservice.jdbc.ThinResultSet.<init>(ThinResultSet.java:107) at org.pentaho.di.trans.dataservice.jdbc.ThinStatement.executeQuery(ThinStatement.java:120) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:308) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:178) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:139) at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:95) at java.lang.Thread.run(Thread.java:722) at org.pentaho.di.trans.dataservice.jdbc.ThinResultSet.<init>(ThinResultSet.java:127) at org.pentaho.di.trans.dataservice.jdbc.ThinStatement.executeQuery(ThinStatement.java:120) ... 5 more Caused by: org.pentaho.di.core.exception.KettleException: No closing " found, starting at location 175 in : [SELECT "KETTLE"."344DATASERVICE"."PRODUCTLINE", SUM(KETTLE"."344DATASERVICE"."SALES") FROM "KETTLE"."344DATASERVICE" GROUP BY "344DATASERVICE"."PRODUCTLINE"] at org.pentaho.di.cluster.HttpUtil.execMethod(HttpUtil.java:138) at org.pentaho.di.cluster.HttpUtil.execService(HttpUtil.java:95) at org.pentaho.di.trans.dataservice.jdbc.ThinResultSet.remoteQuery(ThinResultSet.java:144) at org.pentaho.di.trans.dataservice.jdbc.ThinResultSet.<init>(ThinResultSet.java:107) ... 6 more {noformat} Removing "Kettle." from the column name in the SELECT, the query will run. {code} SELECT "344DataService"."PRODUCTLINE", sum("344DataService"."SALES") FROM "Kettle"."344DataService" GROUP BY "344DataService"."PRODUCTLINE" {code}

    Pentaho BI Platform Tracking | 1 year ago | Kurtis Cruzada
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT "Kettle"."344DataService"."PRODUCTLINE", sum(Kettle"."344DataService"."SALES") FROM "Kettle"."344DataService" GROUP BY "344DataService"."PRODUCTLINE"
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    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

    Pentaho BI Platform Tracking | 10 years ago | p_thiyagu
    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.

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.SQLException

      Could not execute the query

      at com.project.common.Person.<init>()
    2. com.project.common
      test.main
      1. com.project.common.Person.<init>(Person.java:100)
      2. com.project.common.test.main(test.java:25)
      2 frames