nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

comparsion in paramter

404040

my problem is when i select parameter in list box it have to compare a value in chart
it have to produce a result...

for example if i am having a one list box i.e select month: jan 2013,feb 2013,mar 2013 and then another list box Period:Last month,Last year and then it have to drop by
Radio Button i)Group Result ii)Individual result

when i select a Jan 2013 from one list box and then i wil select Last Month or Last year in another list box
so result will be Jan 2013 total value and DEC 2012 total value for Last Month
likewise then i will select Last Year Jan 2013 total value and Jan 2012 total value have to display....

i have attached a sample db for u r reference

thank u in advance

Find more posts tagged with

Comments

404040

sir kindly reply for this post....

thank u ...

404040

sir any idea to do this report....

thank u..

mwilliams

You should just be able to use functions like:

BirtDateTime.addMonth(yourDate,-1)
and
BirtDateTime.addYear(yourDate,-1)

to find the dates a year or a month prior to the selected month, then use those dates in your query.

404040

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="117442" data-time="1370641109" data-date="07 June 2013 - 02:38 PM">
You should just be able to use functions like: 
 
BirtDateTime.addMonth(yourDate,-1) 
and 
BirtDateTime.addYear(yourDate,-1) 
 
to find the dates a year or a month prior to the selected month, then use those dates in your query. </blockquote>
 
thank u very much for reply i have tried this way ...but i cant able to get appropriate value of parameter 
 
so can u able to produce a sample db example for this...its helpful for me ... 
 
 
thank in advance...

mwilliams

Take a look at this modified version of your attached report. I set markers in the query of the dataSet and replaced them in the beforeOpen script based on the selected parameters.

404040

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="117492" data-time="1370878605" data-date="10 June 2013 - 08:36 AM">
Take a look at this modified version of your attached report. I set markers in the query of the dataSet and replaced them in the beforeOpen script based on the selected parameters. </blockquote>
 
Thanks a lot, mwilliams! That was exactly what I was looking for! 
 
but i am getting error while i combine with my report ....and i check with sample db too ..same error is showing ...so i had attached a sample db here for u r reference... 
 
 
Error: 
org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report. 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:299) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:261) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:72) 
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:68) 
at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:365) 
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121) 
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details: 
Failed to prepare the query execution for the data set: msrp 
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet). 
[SELECT distinct country,state,city,CLASSICMODELS.ORDERS.ORDERNUMBER, 
YEAR (CLASSICMODELS.ORDERS.ORDERDATE) AS YEARNUMBER, 
MONTH (CLASSICMODELS.ORDERS.ORDERDATE) AS MONNUMBER, 
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED, 
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, 
msrp, 
buyprice 
FROM CLASSICMODELS.ORDERS, 
CLASSICMODELS.ORDERDETAILS, 
CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.PRODUCTS 
WHERE 
CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE 
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER 
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and (YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p11 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p12) 
OR 
(YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p21 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p22) 
 
 
] 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:654) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:330) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:290) 
... 5 more 
Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: msrp 
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet). 
[SELECT distinct country,state,city,CLASSICMODELS.ORDERS.ORDERNUMBER, 
YEAR (CLASSICMODELS.ORDERS.ORDERDATE) AS YEARNUMBER, 
MONTH (CLASSICMODELS.ORDERS.ORDERDATE) AS MONNUMBER, 
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED, 
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, 
msrp, 
buyprice 
FROM CLASSICMODELS.ORDERS, 
CLASSICMODELS.ORDERDETAILS, 
CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.PRODUCTS 
WHERE 
CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE 
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER 
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and (YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p11 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p12) 
OR 
(YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p21 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p22) 
 
 
] 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:350) 
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:455) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:153) 
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:634) 
... 7 more 
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet). 
[SELECT distinct country,state,city,CLASSICMODELS.ORDERS.ORDERNUMBER, 
YEAR (CLASSICMODELS.ORDERS.ORDERDATE) AS YEARNUMBER, 
MONTH (CLASSICMODELS.ORDERS.ORDERDATE) AS MONNUMBER, 
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED, 
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, 
msrp, 
buyprice 
FROM CLASSICMODELS.ORDERS, 
CLASSICMODELS.ORDERDETAILS, 
CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.PRODUCTS 
WHERE 
CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE 
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER 
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and (YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p11 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p12) 
OR 
(YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p21 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p22) 
 
 
] 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:54) 
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108) 
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:298) 
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareStatement(Connection.java:197) 
at org.eclipse.birt.data.engine.executor.DataSource.prepareStatement(DataSource.java:302) 
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:282) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:459) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:346) 
... 12 more 
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.report.data.oda.jdbc.Statement.prepare(Statement.java:135) 
at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.prepare(BidiStatement.java:52) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doPrepare(OdaQuery.java:229) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.prepare(OdaQuery.java:186) 
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:290) 
... 17 more 
Caused by: java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) 
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source) 
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.prepare(Statement.java:131) 
... 21 more 
Caused by: java.sql.SQLException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) 
... 35 more 
Caused by: ERROR 42X04: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) 
at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.OrNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source) 
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source) 
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) 
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source) 
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) 
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) 
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) 
... 29 more 
 
Error: 
org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report. 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:299) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:261) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:72) 
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:68) 
at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:365) 
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121) 
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details: 
Failed to prepare the query execution for the data set: msrp 
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet). 
[SELECT distinct country,state,city,CLASSICMODELS.ORDERS.ORDERNUMBER, 
YEAR (CLASSICMODELS.ORDERS.ORDERDATE) AS YEARNUMBER, 
MONTH (CLASSICMODELS.ORDERS.ORDERDATE) AS MONNUMBER, 
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED, 
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, 
msrp, 
buyprice 
FROM CLASSICMODELS.ORDERS, 
CLASSICMODELS.ORDERDETAILS, 
CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.PRODUCTS 
WHERE 
CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE 
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER 
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and (YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p11 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p12) 
OR 
(YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p21 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p22) 
 
 
] 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:654) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:330) 
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:290) 
... 5 more 
Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: msrp 
Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet). 
[SELECT distinct country,state,city,CLASSICMODELS.ORDERS.ORDERNUMBER, 
YEAR (CLASSICMODELS.ORDERS.ORDERDATE) AS YEARNUMBER, 
MONTH (CLASSICMODELS.ORDERS.ORDERDATE) AS MONNUMBER, 
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED, 
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, 
msrp, 
buyprice 
FROM CLASSICMODELS.ORDERS, 
CLASSICMODELS.ORDERDETAILS, 
CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.PRODUCTS 
WHERE 
CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE 
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER 
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and (YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p11 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p12) 
OR 
(YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p21 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p22) 
 
 
] 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:350) 
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:455) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:153) 
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:634) 
... 7 more 
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Failed to prepare the following query for the data set type (org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet). 
[SELECT distinct country,state,city,CLASSICMODELS.ORDERS.ORDERNUMBER, 
YEAR (CLASSICMODELS.ORDERS.ORDERDATE) AS YEARNUMBER, 
MONTH (CLASSICMODELS.ORDERS.ORDERDATE) AS MONNUMBER, 
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED, 
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, 
msrp, 
buyprice 
FROM CLASSICMODELS.ORDERS, 
CLASSICMODELS.ORDERDETAILS, 
CLASSICMODELS.CUSTOMERS, 
CLASSICMODELS.PRODUCTS 
WHERE 
CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS.ORDERDETAILS.PRODUCTCODE 
AND CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER 
AND CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER 
and (YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p11 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p12) 
OR 
(YEAR(CLASSICMODELS.ORDERS.ORDERDATE) = p21 
AND MONTH(CLASSICMODELS.ORDERS.ORDERDATE) = p22) 
 
 
] 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:54) 
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108) 
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:298) 
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareStatement(Connection.java:197) 
at org.eclipse.birt.data.engine.executor.DataSource.prepareStatement(DataSource.java:302) 
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:282) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:459) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:346) 
... 12 more 
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement. 
SQL error #1:Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
; 
java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.eclipse.birt.report.data.oda.jdbc.Statement.prepare(Statement.java:135) 
at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.prepare(BidiStatement.java:52) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doPrepare(OdaQuery.java:229) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.prepare(OdaQuery.java:186) 
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:290) 
... 17 more 
Caused by: java.sql.SQLSyntaxErrorException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) 
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source) 
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) 
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) 
at org.eclipse.birt.report.data.oda.jdbc.Statement.prepare(Statement.java:131) 
... 21 more 
Caused by: java.sql.SQLException: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) 
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) 
... 35 more 
Caused by: ERROR 42X04: Column 'P11' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P11' is not a column in the target table. 
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) 
at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.OrNode.bindExpression(Unknown Source) 
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source) 
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source) 
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) 
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source) 
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) 
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) 
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) 
... 29 more 
 
 
can u check this error and provide solution for this... 
 
 
thank in advance..

mwilliams

It would appear that you're not replacing the markers 'p11', 'p12', etc in the query. Make sure you do something similar to the beforeOpen script I had in the sample.