ORA-00904 Invalid Identifier on SQL query

0

 I'm new to posting questions, so I'll try to be as complete as possible.

 

I'm currently building my reports with the iReport Designer 4.0.0, but the machine / program I'm deploying to still uses the 3.1.2 jars.  I have set the compatibility in the Designer options so it will work with 3.1.2.

 

The java code creates the SQL query and generates the report in the code using JasperFillManager(java.io.InputStream, java.util.Map, java.sql.Connection), passing the query in through a HashMap.

 

The original report and query work just fine, the problem I've run into has come up after adding a new column to a table the query accesses, and changing the sql query, I'm getting java.sql.SQLSyntaxErrorException: ORA-00904:  invalid identifier exception.

I'm still using the original report, no changes there.

 

My original SQL query is:

 

SELECT TABLE_1.COLUMN_1, TABLE_1.COLUMN_2, TABLE_1.COLUMN_3, TABLE_1.COLUMN_4, TABLE_1.COLUMN_5, TABLE_1.COLUMN_6, TABLE_1.COLUMN_7,TABLE_1.COLUMN_8,

(CASE WHEN TABLE_2.COLUMN_9 IS NULL THEN 0 ELSE TABLE_2.COLUMN_9 END) VOIDTYPE,

(CASE WHEN TABLE_2.COLUMN_10 IS NULL THEN 0 ELSE TABLE_2.COLUMN_10 END) REFUNDAMOUNT,

(CASE WHEN TABLE_2.COLUMN_11 IS NULL THEN 0 ELSE TABLE_2.COLUMN_11 END) ORIGINALFINE,

(CASE WHEN TABLE_2.COLUMN_12 IS NULL THEN 0 ELSE TABLE_2.COLUMN_12 END) NEWFINE FROM TABLE_1

LEFT OUTER JOIN TABLE_2 ON TABLE_1.COLUMN_2 = TABLE_2.COLUMN_2

WHERE (TABLE_1.COLUMN_13 = -2 OR TABLE_2.COLUMN_9 > 0)

AND TABLE_1.COLUMN_8 BETWEEN $startDate$ AND $endDate$ order by COLUMN_1

 

$startDate$ and $endDate$ get replaced with valid dates by the java code.

 

What has happened is I've added a column, (COLUMN_14) to TABLE_2 via:

ALTER TABLE TABLE_2 ADD COLUMN_14 VARCHAR2(8) DEFAULT 'true'

I've also added one line to the sql query: AND (TABLE_2.COLUMN_14 IS NULL OR TABLE_2.COLUMN_14='true') so that the final query looks like this:

 

SELECT TABLE_1.COLUMN_1, TABLE_1.COLUMN_2, TABLE_1.COLUMN_3, TABLE_1.COLUMN_4, TABLE_1.COLUMN_5, TABLE_1.COLUMN_6, TABLE_1.COLUMN_7,TABLE_1.COLUMN_8,

(CASE WHEN TABLE_2.COLUMN_9 IS NULL THEN 0 ELSE TABLE_2.COLUMN_9 END) VOIDTYPE,

(CASE WHEN TABLE_2.COLUMN_10 IS NULL THEN 0 ELSE TABLE_2.COLUMN_10 END) REFUNDAMOUNT,

(CASE WHEN TABLE_2.COLUMN_11 IS NULL THEN 0 ELSE TABLE_2.COLUMN_11 END) ORIGINALFINE,

(CASE WHEN TABLE_2.COLUMN_12 IS NULL THEN 0 ELSE TABLE_2.COLUMN_12 END) NEWFINE FROM TABLE_1

LEFT OUTER JOIN TABLE_2 ON TABLE_1.COLUMN_2 = TABLE_2.COLUMN_2

WHERE (TABLE_1.COLUMN_13 = -2 OR TABLE_2.COLUMN_9 > 0)

AND (TABLE_2.COLUMN_14 IS NULL OR TABLE_2.COLUMN_14='true')

AND TABLE_1.COLUMN_8 BETWEEN $startDate$ AND $endDate$ order by COLUMN_1

 

I can run this sql statement in sqlplus, as well as a netbeans IDE that is installed on that machine.  When I run the java program (logged in as the same user I tried on sqlplus and netbeans) and run the report, I get this stacktrace:

 

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Report_1

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)

at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:668)

at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588)

at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1212)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:842)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:791)

at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)

at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)

at com.foo.run(foo.java:426)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "TABLE_2"."COLUMN_14": invalid identifier

 

I was wondering if anyone had any ideas why this query works in sqlplus and in netbeans, but not when I try to run the report.  I'm sure I've probably made a stupid mistake somewhere, but any help would be appreciated, thank you.

 
tcswalker's picture
Joined: Jul 4 2012 - 11:50am
Last seen: 2 years 1 month ago

1 Answer:

0

 Is this query in a subreport, maybe "Using Cache" is enabled?

Does it work (return the columns) with a simple "Read Fields" inside the report designer environment?

If you just select that column only from the table in the a new report as a test does it work?

Seems like the report connection doesn't recognize the referenced column, just trying to give some ideas on how to try and find the problem.. 

good luck.

cmatthews's picture
Joined: Nov 18 2007 - 11:14am
Last seen: 1 year 10 months ago
Feedback
randomness