Skip to Content
0
Former Member
Feb 25, 2009 at 02:05 PM

My query (Oracle) doesn't work in Crystal10.What I need to change in query?

42 Views

I create new report using Wizard, Add Command (Oracle query).

I see table in my report, I can run ... Then I save my report and close it.

But when I try to open again -- I can't open again and I see error: "Failed to open document. Read past end TSLV record."

Please, help me ! :((((

My query:


SELECT 

 'Building ' || Rtrim(u.sField7),
 decode(t.iStatus, 1, 'Past', 'Current'),
 u.scode /*Unit*/,
 rtrim(t.sFirstName) || Decode(t.sFirstName,Null,'',', ') || rtrim(t.sLastname) /*Tenant Name*/,
        1,
sum(Decode(sign({?postto} - tr.uPostDate),0,
    Decode(tr.iType, 7,nvl(tr.sTotalAmount,0),0),0))  /*Current Charges*/,

sum(Decode(sign({?postto} - tr.uPostDate),0,
    Decode(tr.iType, 6,nvl(d.sAmount,0),0),0))  /*Current Payments*/,

Max(Decode(Tr.iType,6,Tr.sDateOccurred,NUll))   /*Last Receipt Date*/,

a.ARREARS       ,
(a.ARREARS - (a.ARREARS30 + a.ARREARS60 + a.ARREARS90 + a.ARREARS120)) ,
a.ARREARS30     , 
a.ARREARS60     , 
a.ARREARS90     , 
a.ARREARS120    , 

sum(Decode(tr.iType, 7,  tr.sTotalAmount, -d.sAmount )) -
sum(Decode(sign({?postto} - tr.uPostDate),0,
    Decode(tr.iType, 7,nvl(tr.sTotalAmount,0),0),0)) +
sum(Decode(sign({?postto} - tr.uPostDate),0,
    Decode(tr.iType, 6,nvl(d.sAmount,0),0),0)) /*prior balance */

FROM 
property p, tenant t, trans tr, unit u, detail d, trans trc,depm_arrears_temp a,v$session v

WHERE 

 tr.iType IN (6, 7)                                           
 AND  d.hinvorrec(+) = tr.hmy
 AND  d.hchkorchg = trc.hmy(+)
 AND  trc.itype(+) = 7
 AND  trc.uPostDate(+) <= {?postto}
 AND  tr.uPostDate(+)  <= {?postto}
 AND  t.hMyPerson = tr.hPerson
 and  t.hMyPerson = a.HTENANT
 and  p.hmy = a.HPROP
 and  trim(a.MACHINE) = trim(v.machine)
 and  userenv('sessionid')=v.audsid
 and  a.MONTH_DATE = {?postto}
 AND  p.hmy = t.hproperty
 and  t.hunit = u.hmy
 AND  t.hProperty > 0
 and  nvl(tr.convert,0) = 0
 and  nvl(trc.convert,0) = 0
and p.hmy = {?hprop}

GROUP BY 

length(Rtrim(u.sField7)),
 'Building ' || Rtrim(u.sField7),
  decode(t.iStatus, 1, 'Past', 'Current'),
         length(rtrim(u.scode)),
         u.scode,
         p.sCode,p.sAddr1,
         t.sLastname,t.sFirstName,
   t.sUnitCode,
   a.ARREARS   ,
   a.ARREARS0      , 
   a.ARREARS30     , 
   a.ARREARS60     , 
   a.ARREARS90     , 
   a.ARREARS120   

HAVING

sum(Decode( tr.iType, 7,  tr.sTotalAmount, -d.sAmount ) )  <> 0
 or
abs(sum(Decode( tr.iType, 7,  tr.sTotalAmount, -d.sAmount )) -
    sum(Decode(sign({?postto} - tr.uPostDate),0,
        Decode(tr.iType, 7,nvl(tr.sTotalAmount,0),0),0)) +
    sum(Decode(sign({?postto} - tr.uPostDate),0,
        Decode(tr.iType, 6,nvl(d.sAmount,0),0),0)) ) <> 0

ORDER BY

'Building ' || Rtrim(u.sField7),
 decode(t.iStatus, 1, 'Past', 'Current')