on 04-21-2009 2:24 PM
Crystal 10. I got error ORA-01722: invalid number. I know that problem when I added {?sunitcode} parameter. The field t.sunitcode CHAR(8 BYTE) and {?sunitcode} in Crystal like string.
Need your help!
select
slastname,
sfirstname,
scode,
sunitcode,
saddr1,
saddr2,
scity,
sstate,
szipcode,
psaddr2,
psaddr1,
hmyperson,
amount
from (SELECT t.slastname slastname,
t.sFirstName sfirstname,
t.scode scode,
t.sunitcode sunitcode,
t.saddr1 saddr1,
t.saddr2 saddr2,
t.scity scity,
t.sstate sstate,
t.szipcode szipcode,
p. saddr2 psaddr2,
p. saddr1 psaddr1,
t.hmyperson hmyperson,
sum(tr.stotalamount) - sum(tr.samountpaid) amount
FROM Tenant T, trans tr , property p
WHERE p.hmy = t.hproperty
and t.hMyPerson = tr.hperson
and tr.itype = 7
and tr.bopen = -1
and t.hproperty = tr.hprop
and tr.hprop = {?hprop}
and t.sunitcode = {?sunitcode}
GROUP BY t.sunitcode,
t.scode,t.sfirstname,t.slastname,t.saddr2,t.scity,t.sstate,t.szipcode, t.saddr1, p.saddr2, p.saddr1, t.hmyperson ) t
where amount>1000
ORDER BY t.scode
Hi
Can you please let us know the following details:
- Exact version of Crystal Reports Designer.
- Exact version and type of Database you are using with Crystal Reports Designer.
- Are you getting this issue while connecting to the database using Native connection? If so, then please try with ODBC or OLEDB connection.
- Can you please run this query at the database level and check whether it is refreshing successfully?
- Please make sure that the parameter is of String data type as the field it is referencing to is of character data type.
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
OLEDB connection. Crystal 10, Oracle 10.
This is script which I run in Oracle 10 -- it's working. Why it's doesn't work in Crystal (I changed parameters for crystal to {? ...})?
select
slastname,
sfirstname,
scode,
sunitcode,
saddr1,
saddr2,
scity,
sstate,
szipcode,
psaddr2,
psaddr1,
hmyperson,
amount
from (SELECT t.slastname slastname,
t.sFirstName sfirstname,
t.scode scode,
t.sunitcode sunitcode,
t.saddr1 saddr1,
t.saddr2 saddr2,
t.scity scity,
t.sstate sstate,
t.szipcode szipcode,
p. saddr2 psaddr2,
p. saddr1 psaddr1,
t.hmyperson hmyperson,
sum(tr.stotalamount) - sum(tr.samountpaid) amount
FROM Tenant T, trans tr , property p
WHERE p.hmy = t.hproperty
and t.hMyPerson = tr.hperson
and tr.itype = 7
and tr.bopen = -1
and t.hproperty = tr.hprop
and tr.hprop = :hprop
and trim(t.sunitcode) = trim(:sunitcode)
GROUP BY t.sunitcode,
t.scode,t.sfirstname,t.slastname,t.saddr2,t.scity,t.sstate,t.szipcode, t.saddr1, p.saddr2, p.saddr1, t.hmyperson ) t
where amount>1000
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.