Hi,
I have a problem in my below SQL,it took much time for getting the result(executio n time is around 10 minutes).This sql contains many functions and also a DISTINCT keyword and its mandatory.So request you to please provide me solution for this. Indexes are exits and also I have analyzed all tables which been used inside this SQL.
I have removed the DISTINCT keyword its gave me result but its return duplicate records so i need it so please provide me the solution ASAP.
Best Regards,
Kiran
I have a problem in my below SQL,it took much time for getting the result(executio n time is around 10 minutes).This sql contains many functions and also a DISTINCT keyword and its mandatory.So request you to please provide me solution for this. Indexes are exits and also I have analyzed all tables which been used inside this SQL.
I have removed the DISTINCT keyword its gave me result but its return duplicate records so i need it so please provide me the solution ASAP.
Code:
SELECT DISTINCT PC.ID,
PC.IDTYPE,
PC.CONSTROLEID,
PC.CONSTROLEPRIMARYYN,
CATMS.MAINCATID,
CATMS.SUBCATID,
MCAT.MAINCATDESC,
SCAT.SUBCATDESC,
B.BENID,
B.BENDESC BENEFICIARY,
P.PROJECTID,
P.PROGRAMID,
SUBSTR(DECODE(P.OPENCLOSEIND, 'C', 'C', 'O', 'O', NULL, 'P'),
1,
1) OPENCLOSEIND,
DECODE(P.OPENCLOSEIND,
NULL,
'In Process',
'C',
'Closed',
'O',
'Open') STATUS_TYPE,
PRJ_GETPROJECTSTATUS_STATUSID(P.PROJECTID) CURRENTSTATUSID,
SL.STATUSID,
SL.STSDESC STATUSDESC,
TRUNC(PS.EFFDT) EFFDT,
PS.ENDDT,
SUBSTR((PRT.PROGTYSDESC || DECODE(P.FINLOCKYN,
'Y',
SUBSTR(P.APPRYEAR, 3, 2),
'__') || P.PROJECTID),
1,
100) PROJECT_ID,
PRT.PROGTYSDESC PROGTYSDESC,
PRT.PROGTYDESC GRANTTYPE,
P.PROJDESC PROJDESC,
RI.NAME TRFSTAFF,
PRJ_GETCOUNTRYNAME(P.LOCCNTRYID) PROJECTCOUNTRY,
SUBSTR(PRJ_GETPROJECTSTATUS(P.PROJECTID), 1, 100) CURRENTSTATUS,
GETLASTPAYMENTDT(P.PROJECTID) LASTPAYMENT_DATE,
GETREPORTEDDT(P.PROJECTID) REPORTEDDATE,
PRJ_GETPROJECTCURRSTATUSDATE(P.PROJECTID) STATUSEFFDATE,
PS.ENDDT STATUSENDDATE,
P.APPRDT APPROVDATE,
P.EFFDT PROJECTSTARTDATE,
P.ENDDT PROJECTENDDATE,
DECODE(P.FINLOCKYN, 'Y', PRJ_GETAWARDAMOUNT(P.PROJECTID), 0) TRFAWARD,
SUBSTR(PROJECTINVOLVEMENT(PC.CONSTROLEPRIMARYYN,
PC.FUNDINGYN),
1,
200) INVOLVEMENT,
CR.CONSTROLEDESC CONST_ROLE,
CT.CONSTTYPEDESC CONST_TYPE,
PRJ_FORMATNAMEFROMID(PC.ID, PC.IDTYPE, 'N') CONST_NAME,
PRJ_GETDISTRICT(PC.ID, PC.IDTYPE, PC.EFFDT) DISTRICT,
PRJ_GETCOUNTRY(PRJ_GETDISTRICT(PC.ID, PC.IDTYPE, PC.EFFDT),
PC.ID,
PC.IDTYPE) CONST_COUNTRY,
PRJ_GETZONEID(PRJ_GETDISTRICT(PC.ID, PC.IDTYPE, PC.EFFDT)) ZONEID,
PRJ_GETCONSTCASH(PC.PROJECTID,
PC.ID,
PC.IDTYPE,
PC.CONSTROLEID) CASHCOMMITTED,
PRJ_GETCONSTDDF(PC.PROJECTID,
PC.ID,
PC.IDTYPE,
PC.CONSTROLEID) DDFCOMMITTED,
PRJ_GETTOTALFUNDING(P.PROJECTID) TOTALFUNDING,
CT.CONSTTYPEID NO_OF_VOLUNTEER,
PRT.PROGTYID NO_TOTALS_IF_IG_3
FROM PROJECT P,
PROJECTCONSTITUENT PC,
PROGRAM PR,
PROGRAMTYPE PRT,
PROJECTSTATUS PS,
PROGRAMPHASESTATUS PPS,
STATUSLIST SL,
CONSTITUENTROLE CR,
CONSTITUENTTYPE CT,
PROJECTBENEFICIARY PB,
BENEFICIARY B,
PROJECTCATEGORY PCAT,
CATEGORYMAINSUB CATMS,
MAINCATEGORY MCAT,
SUBCATEGORY SCAT,
ADMINUSERDATA RI
WHERE P.PROJECTID = PC.PROJECTID
AND P.PROGRAMID = PR.PROGRAMID
AND PR.PROGTYID = PRT.PROGTYID
AND P.PROJECTID = PS.PROJECTID
AND PS.PROGPHASESTATUSID = PPS.PROGPHASESTATUSID
AND PPS.STATUSID = SL.STATUSID
AND P.PROJECTID = PB.PROJECTID(+)
AND PB.BENID = B.BENID(+)
AND P.PROJECTID = PCAT.PROJECTID(+)
AND PCAT.CATID = CATMS.CATID(+)
AND CATMS.MAINCATID = MCAT.MAINCATID(+)
AND CATMS.SUBCATID = SCAT.SUBCATID(+)
AND PC.CONSTROLEID = CR.CONSTROLEID
AND PC.CONSTTYPEID = CT.CONSTTYPEID
AND P.ADMINUSERID = RI.ADMINUSERID(+)
AND CATMS.MAINCATID = 5
AND CATMS.SUBCATID = 12
AND P.OPENCLOSEIND = 'C'
Best Regards,
Kiran
Comment