Report query "Read Fields" fails but query OK

[2005 IR Help]

By: David Karnowski - karnowski

Report query "Read Fields" fails but query OK

2005-11-08 13:31

Hi,

 

Running iReports 0.5.2 against an Oracle database data source.

 

Setting up the report query and click the "Read Fields' button fails with the following error:

SQL problems:ORA-01722: invalid number

But when I click the "Send to clipboard" button and copy the query to a SQL command line tool (SQL*Plus) it runs just fine. I can also run the report OK with iReport (with no defined fields in the output) using this query, it's only the "Read Fields" button that produces the error.

 

Here's the query I have, all parameters are Strings:

select p.contractid,

p.voucher_no,

p.status,

p.actual_date_paid,

p.usd_amount,

p.bank_code,

p.reference,

c.reason,

'New' flag,

p.userid,

p.date_modified,

'Amended Payments' dml_type,

p.seqno,

p.usd_salestax,

p.tax_date_paid,

p.tax_reference,

p.tax_bank_code

from payments_plus_log p, payments_change_reason c

where p.voucher_no = c.voucher_no(+)

and p.dml_type = 'N'

and (( instr($P{oid},'%') = 0 and p.contractid = $P{oid}) or

( instr($P{oid},'%') != 0 and p.contractid like $P{oid}))

and trunc(p.date_modified)

between to_date($P{lodate}, 'DD-MON-YYYY') and to_date($P{hidate}, 'DD-MON-YYYY')

and p.seqno = c.seqno(+)

and p.dml_type = 'N'

and p.voucher_no between to_number($P{lonum}) and to_number($P{hinum})

union

select p.contractid,

p.voucher_no,

p.status,

p.actual_date_paid,

p.usd_amount,

p.bank_code,

p.reference,

'' reason,

'Old' flag,

p.userid,

p.date_modified,

'Amended Payments' dml_type,

p.seqno,

p.usd_salestax,

p.tax_date_paid,

p.tax_reference,

p.tax_bank_code

from payments_plus_log p

where trunc(p.date_modified)

between to_date($P{lodate}, 'DD-MON-YYYY') and to_date($P{hidate}, 'DD-MON-YYYY')

and p.voucher_no between to_number($P{lonum}) and to_number($P{hinum})

and p.dml_type = 'O'

and (( instr($P{oid},'%') = 0 and p.contractid = $P{oid} ) or

( instr($P{oid},'%') != 0 and p.contractid like $P{oid} ))

union

select p.contractid,

p.voucher_no,

p.status,

p.actual_date_paid,

p.usd_amount,

p.bank_code,

p.reference,

c.reason,

' ' flag,

p.userid,

p.date_modified,

'Deleted Payments' dml_type,

p.seqno,

p.usd_salestax,

p.tax_date_paid,

p.tax_reference,

p.tax_bank_code

from payments_plus_log p, payments_change_reason c

where p.voucher_no = c.voucher_no(+)

and p.dml_type = 'D'

and p.seqno = c.seqno(+)

and p.voucher_no between to_number($P{lonum}) and to_number($P{hinum})

and trunc(p.date_modified)

between to_date($P{lodate}, 'DD-MON-YYYY') and to_date($P{hidate}, 'DD-MON-YYYY')

and (( instr($P{oid},'%') = 0 and p.contractid = $P{oid} ) or

( instr($P{oid},'%') != 0 and p.contractid like $P{oid}))

order by 12,1,2, 13,3,11

 

Any ideas?

 

Thanks,

DavidK

 

 

 

 

 

By: David Karnowski - karnowski

RE: Report query "Read Fields" fails

2005-11-09 11:09

An update, I've seen this fail similarly with other queries as well. For example the query at the bottom fails with an ORA-1858 error ("a non-numeric character was found where a numeric was expected").

 

If I change this line:

and c.clientid like $P{cid}

to read:

and c.clientid = $P{cid}

or to read:

and c.clientid like '%'

then the "Read Fields" works fine. So my work-around to this problem is:

1) Turn off "Automatically Retrieve fields"

2) Put in desired SQL and see if "Read fields" fails

3) If it fails then manipulate the "where" clause replacing values (usually parameters or near parameters) and keep trying "Read fields" until it works.

4) Replace the modified (and undesired) SQL with my original SQL (step 2 above).

5) Exit the "Report query" window without hitting "Read fields" again.

 

Anyway, I have a workaround but this seems like a bug to me.

 

Here's the current full query that produced the error:

select c.clientid, c.cl_name, o.cins, p.description, sum(a.ptq) pricetimesquantity

from orders o, client c, spproduct p, ordalloc a

where o.tradedate >= to_date($P{lodate}, 'DD-MON-YYYY')

and o.tradedate < to_date($P{hidate}, 'DD-MON-YYYY') + 1

and o.clientid = c.clientid

and c.clientid like $P{cid}

and o.eo like $P{loc}

and o.orderid = a.orderid

and o.cins = p.cins(+)

group by c.clientid, c.cl_name, o.cins, p.description