Hi guys
actually i still rookies with DeskI and right now i have a problem with 1 of report that being produced by previous developer and suddenly this report is not generated in BO folder.
after take alook on the report, i notice the sql queries is not properly working
SELECT DISTINCT
convertaccount(CDD01.NUMCPT),
CLD01.NOMCLI,
CCD01.SOLCRA,
CDD05.TYPENR,
CLD01.PRNCLI,
MAX(CDD33.MONRBT_CAP+CDD33.MONRBT_INT),
CLD01.NOMALP,
FCD03.LIBETT_1,
CDD06.LIBPDT_1,
CDD05.CMPSOL_22 + CDD05.CMPSOL_14 + CDD05.CMPSOL_50,
CDD06.CODPDT,
CCD012.SOLCRA,
FCD03.CODETT,
convertdate(CDD01.DATPRE_ECH),
CLD01.TELPRI,
CLD01.TELBUR,
CLD013.NOMCLI,
CASE WHEN ZZ055.CodTB1 = '05' THEN ZZ055.LIBEL1_1 ELSE ' ' END ,
Table__60.LIBEL1_1,
Table__60.LIBEVT_1,
Table__60.LIBEVT_2
FROM
CDD01,
CLD01,
CCD01,
CDD05,
CDD33,
FCD03,
CDD06,
CCD01 CCD012,
CLD01 CLD013,
ZZ055,
( SELECT FCD05.cleevt, ZZ155.LIBEL1_1, FCD05.LIBEVT_1 , FCD05.LIBEVT_2, FCD05.CODUSR,FCD05.datevt
FROM FCD05
LEFT OUTER JOIN ZZ155 ON FCD05.TYPEVT = substr(ZZ155.ARGTBL,3,3)
AND substr(ZZ155.ARGTBL,1,2) = 'IC'
INNER JOIN
( select cleevt as numcpt,
max(concat(fcd05.datevt, fcd05.heumaj)) as latest_event_time
from fcd05
group by cleevt
) t2 ON t2.numcpt = FCD05.cleevt
WHERE concat(fcd05.datevt, fcd05.heumaj) = t2.latest_event_time ) Table__60,
( SELECT CGD30.NUMCPT, ConvertDate(CGD30.DATVAL) AS DATVAL, CGD30.MONMVT
FROM CGD30
JOIN ZZ137 ON CGD30.LIBSTD = ZZ137.ARGTBL AND ZZ137.SWIT05 = 'Y'
JOIN (
SELECT numcpt, max(concat(CGD30.DATVAL, CGD30.heumaj)) AS DATMAX from cgd30
JOIN ZZ137 ON CGD30.LIBSTD = ZZ137.ARGTBL AND ZZ137.SWIT05 = 'Y'
WHERE CGD30.sgnmvt ='C'
group by numcpt) X ON X.Numcpt = CGD30.NUMCPT and concat(CGD30.DATVAL, CGD30.heumaj) = X.DATMAX
WHERE CGD30.sgnmvt ='C' ) Table__63,
CLD04,
CDD20,
ICD01,
ZSD02
WHERE
( CDD05.NUMCPT(+)=CDD01.NUMCPT )
AND ( CDD01.NUMCPT=CDD33.NUMCPT(+) )
AND ( CDD01.NUMCPT=ZSD02.NUMCPT(+) )
AND ( ICD01.NUMCPT=CDD01.NUMCPT )
AND ( CDD01.NUMCPT(+)=CCD01.NUMCPT )
AND ( CDD01.CODPDT=CDD06.CODPDT )
AND ( CCD01.ENTITE=FCD03.CODETT )
AND ( CLD01.NUMCLI=CCD01.NUMCLI(+) )
AND ( CCD012.NUMCPT=ICD01.REFOPN(+) )
AND ( CDD20.NUMCPT(+)=CDD01.NUMCPT )
AND ( CLD01.NUMCLI=CLD04.NUMCLI_1(+) )
AND ( CLD013.NUMCLI(+)=CLD04.NUMCLI_2 )
AND ( CDD01.NUMCPT=Table__60.CLEEVT(+) )
AND ( Table__63.NUMCPT(+)=CDD01.NUMCPT )
AND ( CLD013.TYPCLI=ZZ055.ARGTBL(+) )
AND (
CDD01.STAOPN BETWEEN '4000' AND '9000'
AND CDD33.SWICAP = 'Y'
AND CDD05.TYPENR = '00'
AND CDD05.CMPSOL_22 + CDD05.CMPSOL_14 + CDD05.CMPSOL_50 < 0
AND (convertaccount(CDD01.NUMCPT) NOT IN (SELECT
Table__63.NUMCPT
FROM
( SELECT CGD30.NUMCPT, ConvertDate(CGD30.DATVAL) AS DATVAL, CGD30.MONMVT
FROM CGD30
JOIN ZZ137 ON CGD30.LIBSTD = ZZ137.ARGTBL AND ZZ137.SWIT05 = 'Y'
JOIN (
SELECT numcpt, max(concat(CGD30.DATVAL, CGD30.heumaj)) AS DATMAX from cgd30
JOIN ZZ137 ON CGD30.LIBSTD = ZZ137.ARGTBL AND ZZ137.SWIT05 = 'Y'
WHERE CGD30.sgnmvt ='C'
group by numcpt) X ON X.Numcpt = CGD30.NUMCPT and concat(CGD30.DATVAL, CGD30.heumaj) = X.DATMAX
WHERE CGD30.sgnmvt ='C' ) Table__63
)
OR CCD01.SOLCRA + CCD012.SOLCRA >= ZSD02.PLAOFF_ORI
OR Table__63.MONMVT < CDD33.MONRBT_CAP+CDD33.MONRBT_INT)
AND (CLD04.TYPREL IS NOT NULL
AND CLD04.TYPREL IN ('03', '05')
OR CLD04.TYPREL IS NULL )
AND (CDD20.TYPCTX IS NOT NULL
AND CDD20.TYPCTX = '00'
OR CDD20.TYPCTX IS NULL
OR CDD20.TYPCTX = ' ')
AND Floor( ABS (CDD05.CMPSOL_22 + CDD05.CMPSOL_14 + CDD05.CMPSOL_50) / (CDD33.MONRBT_CAP+MONRBT_INT))
> 0 AND (CDD05.CMPSOL_22 + CDD05.CMPSOL_14 + CDD05.CMPSOL_50 < 0 AND (CDD33.MONRBT_CAP+MONRBT_INT) > 0)
)
GROUP BY
convertaccount(CDD01.NUMCPT),
CLD01.NOMCLI,
CCD01.SOLCRA,
CDD05.TYPENR,
CLD01.PRNCLI,
CLD01.NOMALP,
FCD03.LIBETT_1,
CDD06.LIBPDT_1,
CDD05.CMPSOL_22 + CDD05.CMPSOL_14 + CDD05.CMPSOL_50,
CDD06.CODPDT,
CCD012.SOLCRA,
FCD03.CODETT,
convertdate(CDD01.DATPRE_ECH),
CLD01.TELPRI,
CLD01.TELBUR,
CLD013.NOMCLI,
CASE WHEN ZZ055.CodTB1 = '05' THEN ZZ055.LIBEL1_1 ELSE ' ' END ,
Table__60.LIBEL1_1,
Table__60.LIBEVT_1,
Table__60.LIBEVT_2
ORDER BY
14
when i query this SQL at Toad. its keep pointing at this place
and then after try to google at internet and found this website.
http://ora-00918.ora-code.com/
i try to fetch the data from here
CDD33.MONRBT_INT
now the error is gone but i try to put this query back to DeskI SQL panel, and try to run back the report but it still pop out the same error message. If you have any suggestion or why this thing happen kindly let me know.
skyjuice (BOB member since 2011-02-17)