whitespace weirdness

[2005 IR Help]

By: Harry LeBlanc - harryleblanc

whitespace weirdness

2004-01-23 14:35

I'm experiencing some strange behavior with a couple of reports. I've got a query in a report with lots of two-pass parameters (the ones with the exclamation, $P!{foo} rather than $P{foo}). The report compiles just fine, but when I try to render the report, Oracle throws an "invalid column name" exception.

Here's the query:

SELECT $P!{GroupTable}.name AS GroupName, Outlet.Name AS OutletName, Address.City, State.State, Invoice, DekInvoice.InvoiceDate, BlocksShipped FROM DekInvoice, State, Outlet, Chain, $P!{MfgTable}, $P!{TruckloadTable}, Address WHERE DekInvoice.Dropoff = Outlet.Outlet $P!{TruckloadWhereClause} $P!{InvoiceStatusWhereClause} $P!{MfgWhereClause} AND DekInvoice.ShippingAddress=Address.Address AND Outlet.ParentCompany=Chain.Chain AND Address.State=State.State AND DekInvoice.InvoiceDate BETWEEN '$P!{StartDate}' AND '$P!{EndDate}' ORDER BY $P!{GroupTable}.name, InvoiceDate, Outlet.Name

 

...and here's the error that's thrown:

java.sql.SQLException: ORA-00904: invalid column name

...with the nested exception at:

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:379)

 

The cooked SQL string (at least, the one displayed by the exception handler) is:

SELECT Chain.name AS GroupName, Outlet.Name AS OutletName, Address.City, State.State, Invoice, DekInvoice.InvoiceDate, BlocksShipped FROM DekInvoice, State, Outlet, Chain, Pick, Truckload, Address WHERE DekInvoice.Dropoff = Outlet.Outlet AND DekInvoice.Truckload=Truckload.Truckload AND Truckload.Pick=Manufacturer.company AND DekInvoice.ShippingAddress=Address.Address AND Outlet.ParentCompany=Chain.Chain AND Address.State=State.State AND DekInvoice.InvoiceDate BETWEEN '12/01/2003' AND '12/31/2003' ORDER BY Chain.name, InvoiceDate, Outlet.Name

 

Now, many of what appear to be whitespace aren't the space (decimal 32, 0x20) at all, but a high-ascii char that looks like a whitespace but isn't (decimal 160, 0xa0). When I copy this into an editor and fill with real whitespaces, the query runs fine in Oracle's sqlplus.

I'm not sure the $P! params are the culprit, as I have another report which has $P! params, and it runs just fine. But something is making Oracle choke on the cooked sql, and that's my best guess as to why.

Anyone have any ideas about this one? I'm running jasperreports 0.5.0, using the iReports front end (version 0.2.2) against an Oracle 9i database, jvm 1.4 on win98 frontend (the oracle database server is running redhat linux, not sure which version).

Thanks for your help -- this is a weird one. You can email me directly at hleblanc@bitstream.net.

[Java_Jasper]

Hello,

 

When I run my reports I get the error:  java.sql.SQLSyntaxErrorException: ORA-00940: "ID" : invalid identifier.

Keep in mind I am running this on the JDeveloper Intergrated Weblogic Server, v11.1.2.1.0

I am using iReport 4.5

 

Funny thing that happens, in iReport all templates compile and query just fine, when I reference them in my applicaiton some work and some dont. Where in the XML file would I be looking to solve this problem.

 

Brian