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"

Pentaho BI Platform Tracking | Kurtis Cruzada | 1 year ago
  1. 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"
  2. 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"
  3. 0

    SQL Problem?

    Oracle Community | 1 decade ago | 807594
    java.sql.SQLException: Could not execute the query
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    SQL Problem?

    Oracle Community | 1 decade ago | 807594
    java.sql.SQLException: Could not execute the query
  6. 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

      Unable to execute query:

      at org.pentaho.di.trans.dataservice.jdbc.ThinStatement.executeQuery()
    2. org.pentaho.di
      ThinStatement.executeQuery
      1. org.pentaho.di.trans.dataservice.jdbc.ThinStatement.executeQuery(ThinStatement.java:123)
      1 frame
    3. org.pentaho.reporting
      JdbcPreviewWorker.run
      1. org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:308)
      2. org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:178)
      3. org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:139)
      4. org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:95)
      4 frames
    4. Java RT
      Thread.run
      1. java.lang.Thread.run(Thread.java:722)
      1 frame