cancel
Showing results for 
Search instead for 
Did you mean: 

error ORA-01722: invalid number

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

Need more info. Are you using a Command Object to use this SQL? If so, create the Parameter using the Command Object Parameter UI rather than creating it yourself in the SQL.

Thank you

Don