Jump to content
Changes to the Jaspersoft community edition download ×

Report query "Read Fields" fails but query OK


2005 IR Help

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...