Getting invalid character when using CAST statements

danielmsu

I've got a query that runs fine inside Oracle SQL Developer (after changing the variable placeholders to actual data), but when brought over into BIRT, I'm getting the following error:

org.eclipse.birt.report.engine.api.EngineException: Cannot get the result set metadata. SQL statement does not return a ResultSet object. SQL error #1: ORA-00911: invalid character

I think I have it narrowed down to the CAST statements in the second half of the datasets I'm unioning. I've double-checked the data types i'm casting into, and they're the same as the top half of the fields in my statement below. Any thoughts or ideas as to why it's failing, or what I can do to correct? Following is the SQL statement.

SELECT fnd.tranx_no,fnd.tranx_date,fnd.proposal,fnd.sort_code,fnd.description,fnd.amount
FROM ae_s_fnd_a fnd JOIN ae_p_pro_e wo ON fnd.proposal=wo.proposal
WHERE fnd.tranx_date BETWEEN ? AND ?
AND fnd.description IN ('TIME CARD APPROVAL','EXTERNAL CHARGE','INVENTORY MATERIAL RELEASE TO WORK ORDER','SHOP STOCK USAGE','PROJECT CONTRACT INVOICE CHARGE','PURCHASE ORDER INVOICE')
AND wo.category NOT IN ('NON-BILLABLE')
AND ((wo.category IN ('ALL BILLABLE')) OR (wo.category IN ('BILL MATERIALS') AND fnd.subledger_type IN ('M','C')))
UNION ALL
SELECT fnd.tranx_no,fnd.tranx_date,CAST(fnd.source_tranx_no AS VARCHAR2(15)),CAST(fnd.source_tranx_item AS VARCHAR2(15)),fnd.description,fnd.amount
FROM ae_s_fnd_a fnd
WHERE fnd.tranx_date BETWEEN ? AND ?
AND fnd.description='ASSET RENTAL RETURN'
ORDER BY 1,2,3;

Hans_vd

Hi Danielsmu,

I'd guess it's the date parameters that go wrong. There's no formatting on them.
Check the format of the BIRT parameter and implement the same format in the query. For example if you are using the format yyyy-mm-dd :

WHERE fnd.tranx_date BETWEEN to_date(?, 'yyyy-mm-dd') AND to_date(?, 'yyyy-mm-dd')


Hope this helps
Hans