[DB2] ลอง Replace Query ใน Stored Procedure กันครับ

เนื่องจากปัญหาในช่วงนี้ตัว Framework ที่วางกันไว้มันเกินข้อจำกัดไปแล้วครับ การเอา SQL ไปยัดลงเป็น String ในฐานข้อมูลเนี่ยย นอกจากมีข้อจำกัดในการทำ HADR แล้ว ถ้า Requirement เปลี่ยนไปจน Query มันมีขนาดเกินจากที่กำหนดไว้นี่แย่แน่เลยครับ หนทางการแก้ไขมีหลายแบบครับ มาลองไล่วิธีที่เคยทำกันก่อนครับ

เอา Query ที่ใข้งานบ่อยๆ JOIN กับเป็นประจำไปทำเป็น View ครับ
ลดงานบางส่วนที่ทำ CTE ไว้ โดยยกไปเป็น Function ครับ
แปลง SQL เดิมๆที่ทำกันไปเป็น Stored Procedure ครับ

สำหรับใน Blog นี้ของเอาวิธีที่ 3 ครับ เนื่องจากข้อมูลในเนตมันน้อยมากครับ และผมอยากจะแก้ Query เดิมให้น้อยที่สุดครับ

เริ่มต้นจาก Query เดิมๆครับ มันมีจุดที่ต้อง Replace Param อยู่หลายจุดเลย
ลองมาแปลงเป็น Store Procedure
แล้วจุดที่ต้อง Replace Param อันนี้ใช้คำสั่ง REPLACE ครับ (ตอนแรกผมก็เชื่อม String แหละ โดยใช้ || เชื่อม แต่ไปๆมาๆมัน Overflow ครับ
มาดู Store Procedure ที่สมบูรณ์กันครับ

SET CURRENT SCHEMA = INVEST;

SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,INVEST;

CREATE OR REPLACE PROCEDURE INVEST.TSY_HOLDINGDATA (
    IN DATEFROM    VARCHAR(30),
    IN DATETO    VARCHAR(30),
    IN PORTIDLIST    VARCHAR(1000),
    IN SECTYPEIDLIST    VARCHAR(1000),
    IN UNITTYPE    VARCHAR(10) )
  SPECIFIC SQL191030160042672
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  READS SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
BEGIN 

-- Declare variable
DECLARE STMT1 CLOB ( 300000 ) ; 
DECLARE C1 CURSOR WITH RETURN TO CALLER FOR S1 ;

SET STMT1 = 'WITH UNIONDATA AS (SELECT SECURITYID,PORTFOLIOID,VALUEDATE,Sum(A.COLLATERALUNIT) AS COLLATERALUNIT,Sum(A.PLEDGEUNIT) AS PLEDGEUNIT,Sum(A.BORROWUNIT) AS BORROWUNIT,Sum(A.LENDUNIT) AS LENDUNIT,Sum(RIGHTUNIT) AS RIGHTUNIT,Sum(TRADEDUNIT) AS TRADEDUNIT,INVESTTXID AS INVESTTXID,ACTION,Max(INVESTMENTDATE) AS INVESTMENTDATE,Max(COMPANYCODE) AS COMPANYCODE,Max(MATUREDATE) AS MATUREDATE,Max(SECURITYLIFE) AS SECURITYLIFE,Max(BASECURRENCY) AS BASECURRENCY,Max(COUPONRATE) AS COUPONRATE,Sum(DIVIDEND) AS DIVIDEND,Max(EXCHANGERATE) AS EXCHANGERATE,Sum(INVESTEDVALUE) AS INVESTEDVALUE,Max(COSTPERUNIT) AS COSTPERUNIT,Max(MARKETUNITCOST) AS MARKETUNITCOST,Sum(BOOKVALUE) AS BOOKVALUE,Sum(LOCALMARKETAMOUNT) AS LMARKETAMT,Sum(MARKETCOST) AS MARKETCOST,Sum(ACCRUEDINT) AS ACCRUEDINT,Sum(SOLDAMORTIZE) AS SOLDAMORTIZE,Sum(ADVANCEDINTEREST) AS ADVANCEDINTEREST,Sum(MATUREVALUE) AS MATUREVALUE,Sum(LOCALBOOKVALUE) AS LBOOKVALUE,Max(YIELD) AS YIELD,Max(ISSHOWRM) AS ISSHOWRM,Sum(REQUIREDMARGIN) AS REQUIREDMARGIN,Sum(DAILYAMORTIZE) AS DAILYAMORTIZE,Sum(UNREALIZEDGAINLOSS) AS UNREALIZEDGAINLOSS,Max(MARKETYIELD) AS MARKETYIELD,Max(MTMRM) AS MTMRM,Max(AVGMARKETYIELD) AS AVGMARKETYIELD,Sum(AVGMARKETCOST) AS AVGMARKETCOST,Max(DURATION) AS DURATION,Max(CONVEXITY) AS CONVEXITY,Sum(BOOKAFTER_DEVALUE) AS BOOKAFTER_DEVALUE,Sum(DEVALUE) AS DEVALUE,Sum(DAILYAI) AS DAILYAI,Max(TTM) AS TTM,Sum(COMMAMOUNT) AS COMMAMOUNT,Sum(QUOTEDAMOUNT) AS QUOTEDAMOUNT,Max(BETA) AS BETA,Sum(LOCALINVESTMENTAMOUNT)AS LINVESTMENTAMT,Sum(ENQ_LASTMONTHCOST) AS ENQ_LASTMONTHCOST,Sum(ALLOWANCE) AS ALLOWANCE,Sum(BOOKAFTERIMPAIRMENT) AS BOOKAFTIMPAIR,Sum(IMPAIRMENTREMAINING) AS IMPAIRMENT,Sum(YIELDEXCCOMM) AS YIELDEXCCOMM,Sum(MARKETUNITCOST15) AS MARKETUNITCOST15 FROM (SELECT COLLATERALID AS SECURITYID,PORTFOLIOID,VALUEDATE,CASE WHEN COLLATERALTYPE = ''COLLATERAL''THEN SUM(ABS(COALESCE(COLLATERALUNIT,0))) ELSE 0 END COLLATERALUNIT,CASE WHEN COLLATERALTYPE = ''PLEDGE''THEN SUM(ABS(COALESCE(COLLATERALUNIT,0)) ) ELSE 0 END PLEDGEUNIT,0 AS BORROWUNIT,0 AS LENDUNIT,0 AS INVESTTXID,'''' AS ACTION,NULL AS INVESTMENTDATE,'''' AS COMPANYCODE,NULL AS MATUREDATE,0 AS SECURITYLIFE,'''' AS BASECURRENCY,0 AS COUPONRATE,0 AS DIVIDEND,0 AS EXCHANGERATE,0 AS INVESTEDVALUE,0 AS COSTPERUNIT,0 AS MARKETUNITCOST,0 AS BOOKVALUE,0 AS LOCALMARKETAMOUNT,0 AS MARKETCOST,0 AS ACCRUEDINT,0 AS SOLDAMORTIZE,0 AS ADVANCEDINTEREST,0 AS MATUREVALUE,0 AS LOCALBOOKVALUE,0 AS YIELD,'''' AS ISSHOWRM,0 AS REQUIREDMARGIN,0 AS DAILYAMORTIZE,0 AS UNREALIZEDGAINLOSS,0 AS MARKETYIELD,0 AS MTMRM,0 AS AVGMARKETYIELD,0 AS AVGMARKETCOST,0 AS DURATION,0 AS CONVEXITY,0 AS BOOKAFTER_DEVALUE,0 AS DEVALUE,0 AS DAILYAI,0 AS TTM,0 AS COMMAMOUNT,0 AS QUOTEDAMOUNT,0 AS RIGHTUNIT,0 AS TRADEDUNIT,0 AS BETA,0 AS LOCALINVESTMENTAMOUNT,0 AS ENQ_LASTMONTHCOST,0 AS ALLOWANCE,0 AS BOOKAFTERIMPAIRMENT,0 AS IMPAIRMENTREMAINING,0 AS YIELDEXCCOMM,0 AS MARKETUNITCOST15 FROM REPOMARGINDETAIL R INNER JOIN SECURITY S ON S.SECURITYID = COLLATERALID AND S.ACTIVEFLAG = ''A''WHERE VALUEDATE BETWEEN @DateFrom AND @DateTo AND PORTFOLIOID IN(@PortIDList) AND SECURITYTYPEID IN( @SecTypeIDList ) GROUP BY COLLATERALID,PORTFOLIOID,COLLATERALTYPE,VALUEDATE UNION ALL SELECT COLLATERALID AS SECURITYID,PORTFOLIOID,VALUEDATE,CASE WHEN COLLATERALTYPE = ''COLLATERAL''THEN SUM(ABS(COLLATERALUNIT)) ELSE 0 END COLLATERALUNIT,CASE WHEN COLLATERALTYPE = ''PLEDGE''THEN SUM(ABS(COLLATERALUNIT)) ELSE 0 END PLEDGEUNIT,CASE WHEN COLLATERALTYPE = ''BORROW''THEN SUM(ABS(COLLATERALUNIT)) ELSE 0 END BORROWUNIT,CASE WHEN COLLATERALTYPE = ''LEND''THEN SUM(ABS(COLLATERALUNIT)) ELSE 0 END LENDUNIT,0 AS INVESTTXID,'''' AS ACTION,NULL AS INVESTMENTDATE,'''' AS COMPANYCODE,NULL AS MATUREDATE,0 AS SECURITYLIFE,'''' AS BASECURRENCY,0 AS COUPONRATE,0 AS DIVIDEND,0 AS EXCHANGERATE,0 AS INVESTEDVALUE,0 AS COSTPERUNIT,0 AS MARKETUNITCOST,0 AS BOOKVALUE,0 AS LOCALMARKETAMOUNT,0 AS MARKETCOST,0 AS ACCRUEDINT,0 AS SOLDAMORTIZE,0 AS ADVANCEDINTEREST,0 AS MATUREVALUE,0 AS LOCALBOOKVALUE,0 AS YIELD,'''' AS ISSHOWRM,0 AS REQUIREDMARGIN,0 AS DAILYAMORTIZE,0 AS UNREALIZEDGAINLOSS,0 AS MARKETYIELD,0 AS MTMRM,0 AS AVGMARKETYIELD,0 AS AVGMARKETCOST,0 AS DURATION,0 AS CONVEXITY,0 AS BOOKAFTER_DEVALUE,0 AS DEVALUE,0 AS DAILYAI,0 AS TTM,0 AS COMMAMOUNT,0 AS QUOTEDAMOUNT,0 AS RIGHTUNIT,0 AS TRADEDUNIT,0 AS BETA,0 AS LOCALINVESTMENTAMOUNT,0 AS ENQ_LASTMONTHCOST,0 AS ALLOWANCE,0 AS BOOKAFTERIMPAIRMENT,0 AS IMPAIRMENTREMAINING,0 AS YIELDEXCCOMM,0 AS MARKETUNITCOST15 FROM SBLMARGINDETAIL R INNER JOIN SECURITY S ON S.SECURITYID = COLLATERALID AND S.ACTIVEFLAG = ''A''WHERE R.VALUEDATE BETWEEN @DateFrom AND @DateTo AND PORTFOLIOID IN(@PortIDList) AND SECURITYTYPEID IN( @SecTypeIDList ) GROUP BY COLLATERALID,PORTFOLIOID,COLLATERALTYPE,VALUEDATE UNION ALL SELECT D.SECURITYID,D.PORTFOLIOID,D.VALUEDATE,0 AS COLLATERALUNIT,0 AS PLEDGEUNIT,0 AS BORROWUNIT,0 AS LENDUNIT,CASE WHEN @UnitType = 1 THEN 0 ELSE D.INVESTTXID END INVESTTXID,D.ACTION,D.INVESTMENTDATE,D.COMPANYCODE,D.MATUREDATE,D.SECURITYLIFE,D.BASECURRENCY,D.COUPONRATE,D.DIVIDEND,D.EXCHANGERATE,D.INVESTEDVALUE,D.COSTPERUNIT,D.MARKETUNITCOST,D.BOOKVALUE,D.LOCALMARKETAMOUNT,D.MARKETCOST,D.ACCRUEDINT,D.SOLDAMORTIZE,D.ADVANCEDINTEREST,D.MATUREVALUE,D.LOCALBOOKVALUE,D.YIELD,D.ISSHOWRM,D.REQUIREDMARGIN,D.DAILYAMORTIZE,D.UNREALIZEDGAINLOSS,D.MARKETYIELD,CASE WHEN D.ISSHOWRM = ''Y''THEN D.MARKETDM ELSE 0 END MTMRM,D.AVGMARKETYIELD,D.AVGMARKETCOST,D.DURATION,D.CONVEXITY,D.LOCALBOOKVALUE - D.DEVALUE AS BOOKAFTER_DEVALUE,D.DEVALUE,D.DAILYAI,D.SECURITYLIFE / 365.00 AS TTM,D.COMMAMOUNT,D.QUOTEDAMOUNT,D.RIGHTUNIT,D.TRADEDUNIT,D.BETA,D.LOCALINVESTMENTAMOUNT,D.ENQ_LASTMONTHCOST,D.ENQ_ALLOWANCE AS ALLOWANCE,D.BOOKAFTERIMPAIRMENT,D.IMPAIRMENTREMAINING,D.YIELDEXCCOMM,D.MARKETUNITCOST15 FROM DAILYACCPOSITION D INNER JOIN SECURITY S ON S.SECURITYID = D.SECURITYID AND S.ACTIVEFLAG = ''A''WHERE D.VALUEDATE BETWEEN @DateFrom AND @DateTo AND D.ACCOUNTSUBTYPE = ''INVEST''AND D.PORTFOLIOID IN(@PortIDList) AND S.SECURITYTYPEID IN( @SecTypeIDList ) )AS A GROUP BY SECURITYID,PORTFOLIOID,VALUEDATE,ACTION,INVESTTXID),DAILYDATA AS (SELECT D.PORTFOLIOID,D.SECURITYID,S.ISSUERID,S.GUARANTORID,INVGM.INVESTGROUPID,RTRIM(P.PORTFOLIOCODE) AS PORTFOLIOCODE,RTRIM(C.CURRENCYCODE) AS PORTCCY,RTRIM(S.SECURITYCODE) AS SECURITYCODE,D.INVESTTXID,D.VALUEDATE,RTRIM(D.ACTION) AS TXACTION,RTRIM(S.SECURITYDESC) AS SECURITYDESC,RTRIM(S.SECURITYDESC2) AS SECURITYDESC2,RTRIM(SC.SECURITYCLASSDESC) AS SECURITYCLASSDESC,RTRIM(INV.INTERNALCODE) AS INTERNALCODE,TX.ISAUTOREDEMP,RTRIM(ST.SECURITYTYPECODE) AS SECURITYTYPECODE,RTRIM(ST.SECURITYTYPEDESC) AS SECURITYTYPEDESC,P.PURPOSE,RTRIM(INS.INSTITUTECODE) AS INSTITUTECODE,D.INVESTMENTDATE,RTRIM(CASE WHEN INV.INTERNALCODE IN( ''FX'',''REPO'',''INTEREST'',''CURRENCY'',''LEND'',''BORROW'',''CASHMARGIN'') THEN D.COMPANYCODE WHEN INV.INTERNALCODE IN( ''IB BORROW'',''IB LEND'' )THEN CP.COUNTERPARTYCODE ELSE I.ISSUERCODE END) AS ISSUERCODE,RTRIM(CASE WHEN INV.INTERNALCODE IN( ''FX'',''REPO'',''INTEREST'',''CURRENCY'',''LEND'',''BORROW'',''CASHMARGIN'') THEN COM.COMPANYNAME WHEN INV.INTERNALCODE IN( ''IB BORROW'',''IB LEND'' )THEN CP.COUNTERPARTYNAME ELSE I.ISSUERNAME END) AS ISSUERNAME,RTRIM(SEF.SECTORCODE) AS SECTORCODE,RTRIM(SEF.SECTORNAME) AS SECTORNAME,RTRIM(G.GUARANTORCODE) AS GUARANTORCODE,CASE WHEN SW.CONTRACTDATE IS NULL THEN F.ISSUEDATE ELSE SW.CONTRACTDATE END ISSUEDATE,D.MATUREDATE,D.SECURITYLIFE,RTRIM(D.BASECURRENCY) AS BASECURRENCY,CASE WHEN SW.SWAPTYPECODE IS NULL THEN F.COUPONTYPE WHEN SW.SWAPTXID > 0 AND D.ACTION = ''PAY''THEN LEFT(RTRIM(SW.SWAPTYPECODE),5) WHEN SW.SWAPTXID > 0 AND D.ACTION = ''RECEIVE''THEN RIGHT(RTRIM(SW.SWAPTYPECODE),5) ELSE ''''END COUPONTYPE,D.COUPONRATE,CASE WHEN F.SECURITYID IS NULL THEN ''''ELSE INVEST.FINDFITYPEOFFEATURE(F.TYPEOFFEATURE) END TYPEOFFEATURE,CASE WHEN TX.TRADEDATE IS NULL THEN 0 ELSE DAYS(D.VALUEDATE) - DAYS(TX.TRADEDATE) END HOLDINGPERIODDAY,D.COLLATERALUNIT,D.PLEDGEUNIT,D.BORROWUNIT,D.LENDUNIT,CASE WHEN ACTION = ''SHORT''THEN 0 ELSE D.RIGHTUNIT END RIGHTUNIT,CASE WHEN ACTION = ''SHORT''THEN D.RIGHTUNIT ELSE 0 END RIGHTSHORTUNIT,CASE WHEN ACTION = ''SHORT''THEN D.TRADEDUNIT ELSE 0 END SHORTUNIT,CASE WHEN ACTION = ''SHORT''THEN 0 ELSE D.TRADEDUNIT END TOTALUNIT,CASE WHEN ACTION = ''SHORT''THEN 0 ELSE D.DIVIDEND * D.EXCHANGERATE END LACCRUEDDIV,CASE WHEN ACTION = ''SHORT''THEN D.DIVIDEND * D.EXCHANGERATE ELSE 0 END LSHORTACCRUEDDIV,D.DIVIDEND * D.EXCHANGERATE AS DIVIDEND,D.INVESTEDVALUE AS INVESTEDVALUE,D.COSTPERUNIT AS COSTPERUNIT,D.MARKETUNITCOST AS MARKETPERUNIT,D.BOOKVALUE,CASE WHEN INV.INTERNALCODE = ''STRUC_NOTE''THEN D.LMARKETAMT ELSE D.MARKETCOST END TMARKETCLEAN,CASE WHEN INV.INTERNALCODE = ''STRUC_NOTE''THEN D.LMARKETAMT WHEN INV.INTERNALCODE IN( ''BOND FTS'',''COMM FTS'',''FX FTS'',''INDEX FTS'',''INT FTS'',''EQ FTS'' )THEN D.MARKETCOST WHEN INV.INTERNALCODE IN( ''BORROW'',''LEND'' )THEN D.MARKETCOST + D.ACCRUEDINT + D.SOLDAMORTIZE + D.ADVANCEDINTEREST - (SELECT SUM(CASE WHEN I.INVESTTXTYPEID = 3 THEN I.SETTLEAMOUNT ELSE I.SETTLEAMOUNT *- 1 END) FROM INVESTTX I INNER JOIN SBL ON SBL.SECURITYID = I.REFSECURITYID AND SBL.ACTIVEFLAG = ''A''AND I.SETTLEDATE = SBL.ISSUEDATE WHERE I.CASHTXTYPEID = 23 AND I.REFSECURITYID = D.SECURITYID AND I.SETTLEDATE <= D.VALUEDATE AND I.ISCANCELLED = ''N''AND I.ISDELETED = ''N'') ELSE D.MARKETCOST + D.ACCRUEDINT + D.SOLDAMORTIZE + D.ADVANCEDINTEREST END TMARKETDIRTY,D.EXCHANGERATE AS FXRATE,D.MATUREVALUE AS FACEAMOUNT,( D.LBOOKVALUE + D.ACCRUEDINT ) * D.EXCHANGERATE * D.YIELD AS BOOKYIELD,( D.LBOOKVALUE + D.ACCRUEDINT ) * D.EXCHANGERATE AS AVGBOOKYIELD,D.ISSHOWRM,( D.LBOOKVALUE + D.ACCRUEDINT ) * D.EXCHANGERATE * D.REQUIREDMARGIN AS BOOKRM,( D.LBOOKVALUE + D.ACCRUEDINT ) * D.EXCHANGERATE AS AVGBOOKRM,CASE WHEN ACTION = ''SHORT''THEN 0 ELSE D.LBOOKVALUE END LCOSTAMOUNTCLEAN,CASE WHEN ACTION = ''SHORT''THEN D.LBOOKVALUE ELSE 0 END LSHORTAMOUNT,D.MARKETCOST * D.EXCHANGERATE AS LMARKETCLEAN,D.ACCRUEDINT,D.LMARKETAMT + ( ( D.ACCRUEDINT + D.SOLDAMORTIZE + D.ADVANCEDINTEREST ) * D.EXCHANGERATE ) AS LMARKET,D.DAILYAMORTIZE * D.EXCHANGERATE AS LAMORTIZE,D.DAILYAMORTIZE,D.UNREALIZEDGAINLOSS * D.EXCHANGERATE AS LUNREALIZEDGAINLOSS,D.MARKETYIELD AS YTM,D.MTMRM,D.AVGMARKETYIELD AS CURRENTYIELD,D.AVGMARKETCOST AS CURRENTCOSTAMTCLEAN,D.AVGMARKETCOST + D.ACCRUEDINT AS CURRENTAMTDIRTY,D.DURATION,D.CONVEXITY,D.BOOKAFTER_DEVALUE,D.DEVALUE,D.DAILYAI,D.TTM,P_EQ.ALPHA1 AS ALPHA,D.BETA,P_EQ.VARIANCE1 AS SD,P_PORTEQ.HPR,P_PORTEQ.XIRR,INVEST.FINDDIVIDENDPOLICY(EQ.SECURITYID,EQ.DVDPOLICY) AS DIVIDENDPOLICY,CASE WHEN EQ.SECURITYID IS NULL THEN''''ELSE GETFUNDCHARACTERISTICCODE(EQ.FUNDCHAR) END FUNDCHARACTERISTIC,CASE WHEN EQ.SECURITYID IS NULL THEN''''ELSE GETINVPOLICYCODE(EQ.INVPOLICY) END FUNDINVESTMENTPOLICY,INVG.NAME AS INVGROUPNAME,INVG.CODE AS INVGROUP,INVEST.FINDALSCOPE(INVG.ISLOCAL,MS.ISLOCAL) AS LOCALGLOBAL,D.COMMAMOUNT AS COMMODITYAMOUNT,D.QUOTEDAMOUNT AS QUOTEDAMOUNT,ST.SECURITYCLASSID,D.LINVESTMENTAMT,( D.MARKETCOST - D.ENQ_LASTMONTHCOST ) * D.EXCHANGERATE AS UNREALIZEDGL_CURRENTCOST,D.ALLOWANCE,D.MARKETUNITCOST15,D.IMPAIRMENT,CASE WHEN P.PURPOSE IN ( 8,99 ) THEN D.BOOKVALUE - D.IMPAIRMENT ELSE D.BOOKVALUE END BOOKAFTIMPAIR,D.ACCRUEDINT + D.SOLDAMORTIZE + D.ADVANCEDINTEREST AS SUMAI,D.MARKETCOST AS TMARKETAFTIMPAIRCLEAN,D.YIELDEXCCOMM FROM UNIONDATA D INNER JOIN PORTFOLIO P ON P.PORTFOLIOID = D.PORTFOLIOID AND P.ACTIVEFLAG = ''A''INNER JOIN SECURITY S ON S.SECURITYID = D.SECURITYID AND S.ACTIVEFLAG = ''A''INNER JOIN SECURITYTYPE ST ON ST.SECURITYTYPEID = S.SECURITYTYPEID AND ST.ACTIVEFLAG = ''A''INNER JOIN INVESTMENT INV ON INV.INVESTMENTID = ST.INVESTMENTID AND INV.ACTIVEFLAG = ''A''INNER JOIN SECURITYCLASS SC ON SC.SECURITYCLASSID = ST.SECURITYCLASSID LEFT OUTER JOIN CURRENCY C ON P.LOCALCURRENCYID = C.CURRENCYID AND C.ACTIVEFLAG = ''A''LEFT OUTER JOIN FIXEDINCOME F ON F.SECURITYID = D.SECURITYID AND F.ACTIVEFLAG = ''A''LEFT OUTER JOIN EQUITY EQ ON EQ.SECURITYID = D.SECURITYID AND EQ.ACTIVEFLAG = ''A''LEFT OUTER JOIN ISSUER I ON I.ISSUERID = S.ISSUERID AND I.ACTIVEFLAG = ''A''LEFT OUTER JOIN GUARANTOR G ON G.COMPANYID = S.GUARANTORID AND G.ACTIVEFLAG = ''A''LEFT OUTER JOIN COMPANY COM ON COM.COMPANYCODE = D.COMPANYCODE AND COM.ACTIVEFLAG = ''A''LEFT OUTER JOIN SECTORFILE SEF ON SEF.SECTORID = S.SECTORID AND SEF.ACTIVEFLAG = ''A''LEFT OUTER JOIN INSTITUTE INS ON INS.INSTITUTEID = ST.INSTITUTEID AND INS.ACTIVEFLAG = ''A''LEFT OUTER JOIN INVESTTX TX ON TX.INVESTTXID = D.INVESTTXID AND TX.SECURITYID = D.SECURITYID AND TX.PORTFOLIOID = D.PORTFOLIOID AND TX.ISDELETED = ''N''AND TX.ISCANCELLED = ''N''LEFT OUTER JOIN COUNTERPARTY CP ON CP.COUNTERPARTYID = TX.COUNTERPARTYID AND CP.ACTIVEFLAG = ''A''LEFT OUTER JOIN PERF_EQDATA P_EQ ON P_EQ.VALUEDATE = D.VALUEDATE AND P_EQ.SECURITYID = D.SECURITYID LEFT OUTER JOIN PERF_PORTFOLIOEQ P_PORTEQ ON P_PORTEQ.PORTFOLIOID = D.PORTFOLIOID AND P_PORTEQ.VALUEDATE = D.VALUEDATE LEFT OUTER JOIN AL_INVESTGROUPMAPPING INVGM ON S.SECURITYTYPEID = INVGM.OBJECTID AND INVGM.ACTIVEFLAG = ''A''LEFT OUTER JOIN AL_INVESTGROUP INVG ON INVG.ID = INVGM.INVESTGROUPID AND INVG.ACTIVEFLAG = ''A''LEFT OUTER JOIN VIEW_AL_MIXSECURITY MS ON MS.SECURITYID = S.SECURITYID LEFT OUTER JOIN SWAPTX SW ON SW.SWAPTXID = D.SECURITYID),SECURITYRATE AS (SELECT TMP1.SECURITYID,LISTAGG(RTRIM(B.GRADENAME),'','')AS GRADENAME,MAX(B.GRADEVALUE) AS GRADEVALUE FROM (SELECT D.SECURITYID,CASE WHEN SECRATE.RATINGDETAILID > 0 THEN SECRATE.RATINGDETAILID WHEN ISURATE.RATINGDETAILID > 0 THEN ISURATE.RATINGDETAILID WHEN GUARATE.RATINGDETAILID > 0 THEN GUARATE.RATINGDETAILID ELSE 0 END RATINGDETAILID FROM DAILYDATA D LEFT OUTER JOIN SECURITYRATING SECRATE ON SECRATE.SECURITYID = D.SECURITYID AND SECRATE.ACTIVEFLAG = ''A''AND SECRATE.VALUEDATE = (SELECT MAX(VALUEDATE) FROM SECURITYRATING R WHERE R.ACTIVEFLAG = ''A''AND R.VALUEDATE <= @DateTo AND R.SECURITYID = D.SECURITYID) LEFT OUTER JOIN ISSUERRATING ISURATE ON ISURATE.ISSUERID = D.ISSUERID AND ISURATE.ACTIVEFLAG = ''A''AND ISURATE.VALUEDATE = (SELECT MAX(VALUEDATE) FROM ISSUERRATING R WHERE R.ACTIVEFLAG = ''A''AND R.VALUEDATE <= @DateTo AND R.ISSUERID = D.ISSUERID) LEFT OUTER JOIN GUARANTORRATING GUARATE ON GUARATE.GUARANTORID = D.GUARANTORID AND GUARATE.ACTIVEFLAG = ''A''AND GUARATE.VALUEDATE = (SELECT MAX(VALUEDATE) FROM GUARANTORRATING R WHERE R.ACTIVEFLAG = ''A''AND R.VALUEDATE <= @DateTo AND R.GUARANTORID = D.GUARANTORID) GROUP BY D.SECURITYID,SECRATE.RATINGDETAILID,ISURATE.RATINGDETAILID,GUARATE.RATINGDETAILID)AS TMP1 LEFT OUTER JOIN RATINGDETAIL B ON B.RATINGDETAILID = TMP1.RATINGDETAILID AND B.ACTIVEFLAG = ''A''GROUP BY TMP1.SECURITYID),ISSUERRATE AS (SELECT TMP1.SECURITYID,RTRIM(MAX(B.GRADENAME))AS GRADENAME,MAX(B.GRADEVALUE) AS GRADEVALUE FROM (SELECT D.SECURITYID,ISURATE.RATINGDETAILID FROM DAILYDATA D LEFT OUTER JOIN ISSUERRATING ISURATE ON ISURATE.ISSUERID = D.ISSUERID AND ISURATE.ACTIVEFLAG = ''A''AND ISURATE.VALUEDATE = (SELECT MAX(VALUEDATE) FROM ISSUERRATING R WHERE R.ACTIVEFLAG = ''A''AND R.VALUEDATE <= @DateTo AND R.ISSUERID = D.ISSUERID) GROUP BY D.SECURITYID,ISURATE.RATINGDETAILID)AS TMP1 LEFT OUTER JOIN RATINGDETAIL B ON B.RATINGDETAILID = TMP1.RATINGDETAILID AND B.ACTIVEFLAG = ''A''GROUP BY TMP1.SECURITYID) SELECT D.VALUEDATE,D.PORTFOLIOCODE,D.PORTCCY,INVEST.FINDPORTFOLIOTYPECODE(D.PURPOSE) AS PURPOSE,INVEST.FINDPORTFOLIOTYPECODETH(D.PURPOSE) AS PURPOSE_TH,D.SECTORCODE,D.SECTORNAME,D.SECURITYTYPECODE,D.SECURITYTYPEDESC,D.SECURITYCODE,D.ISSUEDATE,D.MATUREDATE,D.COUPONRATE,D.COUPONTYPE,D.TYPEOFFEATURE,D.HOLDINGPERIODDAY,D.ISSUERCODE,D.ISSUERNAME,D.INSTITUTECODE,D.GUARANTORCODE,D.INVESTMENTDATE,D.TXACTION,SUM(D.TOTALUNIT) AS TOTALUNIT,SUM(D.SHORTUNIT) AS SHORTUNIT,SUM(D.BORROWUNIT) AS BORROWUNIT,SUM(D.LENDUNIT) AS LENDUNIT,SUM(D.COLLATERALUNIT) AS COLLATERALUNIT,SUM(D.PLEDGEUNIT) AS PLEDGEUNIT,SUM(D.RIGHTUNIT) AS RIGHTUNIT,SUM(D.RIGHTSHORTUNIT) AS RIGHTSHORTUNIT,D.BASECURRENCY AS TRANSCCY,D.SECURITYCLASSDESC,D.INTERNALCODE,D.ISAUTOREDEMP,SUM(D.INVESTEDVALUE) AS INVESTEDVALUE ,SUM(D.FACEAMOUNT) AS FACEAMOUNT,ROUND(CASE WHEN SUM(D.BOOKYIELD) = 0 OR SUM(D.AVGBOOKYIELD) = 0 THEN 0 ELSE SUM(D.BOOKYIELD) / SUM(D.AVGBOOKYIELD) END,6) AS AVGBOOKYIELD,CASE WHEN @UnitType = 1 THEN SUM(CASE WHEN D.TOTALUNIT <> 0 THEN CAST(D.BOOKVALUE AS DECIMAL(24,6)) / D.TOTALUNIT ELSE 0 END) ELSE SUM(D.COSTPERUNIT) END COSTPERUNIT,SUM(D.MARKETPERUNIT) AS MARKETPERUNIT ,SUM(D.BOOKVALUE) AS TCOSTAMOUNTCLEAN,SUM(D.ACCRUEDINT) AS AI,SUM(D.BOOKVALUE + D.ACCRUEDINT) AS TCOSTAMOUNTDIRTY,CASE WHEN D.SECURITYCLASSID = 2 AND D.PURPOSE IN( 3,4,8,99 )THEN SUM(D.TMARKETCLEAN - D.DEVALUE) ELSE SUM(D.TMARKETCLEAN) END TMARKETAMOUNTCLEAN,CASE WHEN D.SECURITYCLASSID = 2 AND D.PURPOSE IN( 3,4,8,99 )THEN SUM(D.TMARKETDIRTY - D.DEVALUE) ELSE SUM(D.TMARKETDIRTY) END TMARKETAMOUNTDIRTY,D.FXRATE,ROUND(CASE WHEN D.ISSHOWRM = ''Y''THEN SUM(D.BOOKRM) / SUM(D.AVGBOOKRM) ELSE 0 END,6) AS AVGBOOKRM,SUM(D.LSHORTAMOUNT) AS LSHORTAMOUNT,SUM(D.LCOSTAMOUNTCLEAN) AS LCOSTAMOUNTCLEAN,SUM(D.LCOSTAMOUNTCLEAN + ( D.ACCRUEDINT * D.FXRATE ))AS LCOSTAMOUNTDIRTY,CASE WHEN D.SECURITYCLASSID = 2 AND D.PURPOSE IN( 3,4,8,99 )THEN SUM(D.LMARKETCLEAN - D.DEVALUE) ELSE SUM(D.LMARKETCLEAN) END AMTCLEAN,SUM(D.ACCRUEDINT * D.FXRATE) AS LOCALAI,CASE WHEN D.SECURITYCLASSID = 2 AND D.PURPOSE IN( 3,4,8,99 )THEN SUM(D.LMARKET - D.DEVALUE) ELSE SUM(D.LMARKET) END AMTDIRTY,SUM(D.LAMORTIZE) AS LAMORTIZE ,CASE WHEN D.PURPOSE = 3 THEN SUM(D.LINVESTMENTAMT - LCOSTAMOUNTCLEAN) ELSE SUM(D.LUNREALIZEDGAINLOSS) END LUNREALIZEDGAINLOSS,SUM(D.LACCRUEDDIV) AS LACCRUEDDIV,SUM(D.LSHORTACCRUEDDIV) AS LSHORTACCRUEDDIV,ROUND(MAX(D.YTM),6) AS YTM,ROUND(MAX(D.MTMRM),6) AS MTMRM,MAX(D.CURRENTYIELD) AS CURRENTYIELD,SUM(D.CURRENTCOSTAMTCLEAN) AS CURRENTCOSTAMTCLEAN,SUM(D.CURRENTAMTDIRTY) AS CURRENTAMTDIRTY,ROUND(MAX(D.DURATION),6) AS DURATION,ROUND(MAX(D.CONVEXITY),6) AS CONVEXITY,SUM(D.BOOKAFTER_DEVALUE) AS BOOKAFTER_DEVALUE,SUM(D.DEVALUE) AS DEVALUEAMOUNT ,D.ALPHA,D.BETA,D.SD,D.HPR,D.XIRR,D.DIVIDENDPOLICY,D.FUNDCHARACTERISTIC,D.FUNDINVESTMENTPOLICY,0 AS ASSETSIZE,D.INVGROUPNAME,D.INVGROUP,D.LOCALGLOBAL,SUM(W.WEIGHT) AS WEIGHT,ROUND(SUM(D.TTM),6) AS TTM,S.GRADENAME AS SECGRADENAME,S.GRADEVALUE AS SECGRADEVALUE ,I.GRADENAME AS ISSUERGRADENAME,I.GRADEVALUE AS ISSUERGRADEVALUE,SUM(D.COMMODITYAMOUNT) AS COMMODITYAMOUNT,SUM(D.QUOTEDAMOUNT) AS QUOTEDAMOUNT,SUM(D.LINVESTMENTAMT) AS LINVESTMENTAMT,SUM(D.UNREALIZEDGL_CURRENTCOST) AS UNREALIZEDGL_CURRENTCOST,SUM(ALLOWANCE) AS ALLOWANCE,SUM(D.DAILYAMORTIZE) AS AMORTIZE,D.SECURITYDESC,D.SECURITYDESC2,D.MARKETUNITCOST15,D.IMPAIRMENT,D.BOOKAFTIMPAIR,D.BOOKAFTIMPAIR * D.FXRATE AS LCOSTAFTIMPAIRCLEAN,D.BOOKAFTIMPAIR + D.SUMAI AS TCOSTAFTIMPAIRDIRTY,( D.BOOKAFTIMPAIR + D.SUMAI ) * D.FXRATE AS LCOSTAFTIMPAIRDIRTY,D.TMARKETAFTIMPAIRCLEAN,D.TMARKETAFTIMPAIRCLEAN * D.FXRATE AS LMARKETAFTIMPAIRCLEAN,D.TMARKETAFTIMPAIRCLEAN + D.SUMAI AS TMARKETAFTIMPAIRDIRTY,( D.TMARKETAFTIMPAIRCLEAN + D.SUMAI ) * D.FXRATE AS LMARKETAFTIMPAIRDIRTY,D.YIELDEXCCOMM FROM DAILYDATA D LEFT OUTER JOIN SECURITYRATE S ON D.SECURITYID = S.SECURITYID LEFT OUTER JOIN ISSUERRATE I ON D.SECURITYID = I.SECURITYID LEFT OUTER JOIN VIEW_AL_MAPMODELPORT W ON D.PORTFOLIOID = W.PORTFOLIOID AND W.INVESTGROUPID = D.INVESTGROUPID GROUP BY D.PORTFOLIOID,D.SECURITYID,D.PORTFOLIOCODE,D.SECURITYCODE,D.VALUEDATE,D.SECURITYDESC,D.SECURITYTYPECODE,D.SECURITYTYPEDESC,D.ISSUERCODE,D.ISSUERNAME,D.SECTORCODE,D.PURPOSE,D.SECTORNAME,D.MATUREDATE,D.SECURITYLIFE,D.TXACTION,D.BASECURRENCY,D.FXRATE,D.INSTITUTECODE,D.HOLDINGPERIODDAY,D.ISSUEDATE,D.COUPONTYPE,D.COUPONRATE,D.ISSHOWRM,D.TYPEOFFEATURE,D.GUARANTORCODE,D.INVESTMENTDATE,D.SECURITYCLASSDESC,D.INTERNALCODE,D.ISAUTOREDEMP,D.ALPHA,D.BETA,D.SD,D.HPR,D.XIRR,D.DIVIDENDPOLICY,D.FUNDCHARACTERISTIC,D.FUNDINVESTMENTPOLICY,S.GRADENAME,S.GRADEVALUE,I.GRADENAME,I.GRADEVALUE,D.INVGROUPNAME,D.INVGROUP,D.LOCALGLOBAL,D.PORTCCY,D.SECURITYCLASSID,D.INVESTTXID,D.SECURITYDESC2,D.IMPAIRMENT,D.BOOKAFTIMPAIR,D.MARKETUNITCOST15,D.SUMAI,D.TMARKETAFTIMPAIRCLEAN,D.YIELDEXCCOMM ORDER BY D.PORTFOLIOCODE,D.SECURITYCODE';
--REPALCE PARAMETER
SET STMT1 = REPLACE(STMT1, '@DateFrom', TRIM(DATEFROM));
SET STMT1 = REPLACE(STMT1, '@DateTo', TRIM(DATETO));
SET STMT1 = REPLACE(STMT1, '@PortIDList', TRIM(PORTIDLIST));
SET STMT1 = REPLACE(STMT1, '@SecTypeIDList', TRIM(SECTYPEIDLIST));
SET STMT1 = REPLACE(STMT1, '@UnitType', TRIM(UNITTYPE));

PREPARE S1 FROM STMT1 ; 
OPEN C1 ; 

END;

ตัวอย่างการเรียกใช้งานครับ

CALL "INVEST"."TSY_HOLDINGDATA"('''2019-03-01'''
                              , '''2019-03-05'''
                              , '137,134,136,121,138,123'
                              , '2040,2073,2099,2006,3004,2051,2052,1001,2007,2079,2076,2041,2093,3011,2010,2011,-98,2054,2042,2053,2017,2019,3002,2020,3003,2043,2071,2072,2069,2070,2014,-2,-1,-3,2084,2089,2086,2088,3005,3006,3007,2082,2087,2085,2090,3001,2083,2091,2021,2044,2056,2055,2057,2059,2058,2060,2005,2096,2097,2094,2078,2062,2061,2063,2065,2064,2066,2045,2092,2003,2098,2046,2016,2067,3008,2068,3009,-99,2004,-100,2047,2048,2015,2009,2008,2001,2002,3012,2049,2050,2037,2036,2029,2028,2033,2032,2025,2024,2031,2030,2023,2022,2035,2034,2027,2026,2018'
                              , '1');

สรุป

จุดเด่น - เอางานไว้กับ Database เร็วแรงแน่นอนครับ เพราะงานเป็นของ DBMS ครับ
จุดด้อย - การ Maintain ยากครับ และมันทำให้ SQL ของเรามันถูก Lock ไปกับ Vendor เจ้าใดเจ้าหนึ่งครับ

Discover more from naiwaen@DebuggingSoft

Subscribe to get the latest posts to your email.

[DB2] ลอง Replace Query ใน Stored Procedure กันครับ

Like this:

Related

Discover more from naiwaen@DebuggingSoft

Share this:

Like this:

Related

Discover more from naiwaen@DebuggingSoft

Related Posts

DB2 Restore backup DB2 database with different database name

DB2 Data Type DECFLOAT แล้ว SQL Server ใช้อะไร ?

DB2 fixed an unpredictable hang/take a lot of time when create a DB Connection.