NO Data rerturned

rugaju

I had previously posted a question about me getting no data back. So far, I have started a new report and the sql i used runs in sql tools but when i run it in BIRT, i get no error but I also get no data. Looking at the log files points to a sql error. I have rewritten my sql many times with different iterations but had no luck. Can anyone help?? Here is the error and the corresponding sql:

ERROR:

Wed Feb 03 13:48:23 CST 2010 ERROR DataSet [dataSet] fetch failed:
java.sql.SQLException: ORA-00933: SQL command not properly ended

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)



SQL:



The SQL (copied from the OPEN) is:

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

sqlText = " SELECT DISTINCT PERSONGROUP "
+ " FROM maximo.persongroup "
// Include the Maximo where clause
+ " where " + params["where"]
+ " Where "
+ " PERSONGROUP NOT IN "
+ " (SELECT PERSONGROUP FROM maximo.persongroupteam) "
;

maximoDataSet.setQuery(sqlText);

SpaceMan

You have two WHERE clauses.<br />
<br />
sqlText = " SELECT DISTINCT PERSONGROUP " <br />
+ " FROM maximo.persongroup "<br />
// Include the Maximo where clause<br />
+ " <strong class='bbc'>where </strong>" + params["where"]<br />
+ " <strong class='bbc'>Where </strong>"<br />
+ " PERSONGROUP NOT IN "<br />
+ " (SELECT PERSONGROUP FROM maximo.persongroupteam) "<br />
;<br />
<br />
Change to:<br />
sqlText = " SELECT DISTINCT PERSONGROUP " <br />
+ " FROM maximo.persongroup "<br />
// Include the Maximo where clause<br />
+ " <strong class='bbc'>where </strong>" + params["where"]<br />
+ " <strong class='bbc'>and</strong> PERSONGROUP NOT IN "<br />
+ " (SELECT PERSONGROUP FROM maximo.persongroupteam) "<br />
;<br />
<br />
FYI - When I have SQL errors, i will open my log and copy my query. Then i'll try to run because BIRT will input all the variables/parameters for you in that log.