URGENT: Report does not run with error Caused by: java.sql.SQLException: ORA-00911: invalid characte

[dirai]

Hi Forum,

Please help me to resolve the problem mentioned in the attached report. A.S.A.P. I guess there is some problem with the input parameters I am passing to this report.

Dipak.

Code:

Caused by: java.sql.SQLException: ORA-00911: invalid character

[doru_voinea]

Check and remove the semicolon at the end of SQL or a the comments (-- /**/) at begin

[dirai]

Hi Doru,

I did and that works. Can you have a look with this query and let me know what's wrong:

 

/************************************************ */
/* MC30 - Stock Check Audit Report */
/*       */
/* REBELJ01 11-APR-2011 ORIGINAL QUERY Written for   */
/*   Swindon RDC Project.  */
/*       */
/************************************************ */
SELECT itl.site_id,
  itl.sku_id,
  itl.location_id,
  TO_CHAR(MAX(dstamp), 'DD-MON-YYYY') latest_check_date,
  COUNT(1) total_stock_checks
FROM
  (SELECT itl_current.site_id,
    itl_current.from_loc_id location_id,
    itl_current.sku_id,
    itl_current.dstamp
  FROM inventory_transaction itl_current
  WHERE itl_current.code = 'Stock Check'
    AND itl_current.site_id = NVL($P{site_id}, itl_archive.site_id)
    AND itl_current.dstamp >= to_timestamp_tz($P{fromdate},'DD-MON-YYYY')
    AND itl_current.dstamp <= to_timestamp_tz($P{todate},'DD-MON-YYYY')
  UNION
  SELECT itl_archive.site_id,
    itl_archive.from_loc_id location_id,
    itl_archive.sku_id,
    itl_archive.dstamp
  FROM inventory_transaction_archive itl_archive
  WHERE itl_archive.code = 'Stock Check'
    AND itl_archive.site_id = NVL($P{site_id}, itl_archive.site_id)
    AND itl_archive.dstamp >= to_timestamp_tz($P{fromdate},'DD-MON-YYYY')
    AND itl_archive.dstamp <= to_timestamp_tz($P{todate},'DD-MON-YYYY')
  )itl
GROUP BY itl.site_id,
  itl.sku_id,
  itl.location_id
ORDER BY 1,2

 

[dirai]

Duro,

 

Please leave it. It is because of multiple table definition in the same query. Sorry!: )