[SQL] เมื่อ JOIN ผสมกับ CASE WHEN

วันนี้ระหว่างทำ DS_FTA ลองอ่าน ฺBOT Manual และก็เริ่มลงมือ Query ดึงข้อมูลเลยครับ พอมีข้อมูลแล้วมีกำลังใจเขียน Code ต่อครับ ไม่งั้นมืดมน เพราะ User เองก็ไม่รู้ว่าต้องส่งอะไรให้ BOT แต่พอเขียน Query ไปได้สัก 80% ช่วงประมาณ 4 โมงเย็น อยู่ๆ มี Idea ทำไมเราไม่ลองใช้ CASE WHEN ในการเขียน JOIN Table หละ ถ้ามันทำได้นี่ มันทำให้เราพลิกแพลงได้เยอะมากครับ

ขั้นแรก มาลองหาข้อมูลกันก่อนว่ามีชาวบ้านเค้าทำไหม ? ปรากฏว่ามีด้วยแหละ

• Can I use CASE statement in a JOIN condition?
• Case Statement on INNER Join

เท่าที่ดูเป็นของ MS SQL Server ที่มี และเท่าที่ดู Syntax มันไม่ได้มีคำสั่งเฉพาะตัวนะ เอาหละ พร้อมจะลุยกันดีกว่า โดยสิ่งที่ผมทำ เป็นลักษณะ ดังนี้ครับ

• แบบเดิม - JOIN แบบปกติ

SELECT A.*
FROM TABLE_A A
  INNER JOIN TABLE_B B
    ON A.COL1 = B.COL1

• แบบใหม่ - ลองใช้ CASE WHEN ครับ

SELECT A.*
FROM TABLE_A A
  INNER JOIN TABLE_B B
    ON A.COL1 = (
      CASE  WHEN A.COL2 = 'XXX' OR A.COL2 = 'YYY' THEN 'N'
            WHEN A.COL2 = 'AAA' OR A.COL2 = 'BBB' THEN 'E'
               ELSE 'Y'
         END
       )

มาลองดูโจทย์ในการทำงานจริงของผมดีกว่า (แต่ปรับย่อมาลงมาครับ) ผมมีโจทย์อยู่ว่า หา Leg Type กับ Transaction ที่มีในระบบครับ

• ข้อมูล Leg Type ของ BOT ครับ

• ข้อมูล Transaction

• ออกแบบ Table

CREATE TABLE INVESTTX(
     `PortfolioCode` varchar(12)
   , `TransactionType` varchar(4)
   , `FIArrangementNumber` varchar(13)
   , `ExchangeRate` Decimal(11,6)
   , `CommodityCurrency` varchar(3)
   , `CommodityAmount` Decimal(24,6)
   , `QuoteCurrency` varchar(3)
   , `QuoteAmount` Decimal(24,6)
);
    
INSERT INTO INVESTTX(
     `PortfolioCode`
   , `TransactionType`
   , `FIArrangementNumber`
   , `ExchangeRate`
   , `CommodityCurrency`
   , `CommodityAmount`
   , `QuoteCurrency`
   , `QuoteAmount`)
VALUES
    ('PING-RISK-MG', 'BUY', 'SO52-20160703', 35.5328, 'THB', 3553280, 'USD', 100000),
    ('PING-RISK-MG', 'SELL', 'SO53-20160703', 35.5328, 'USD', 100000, 'THB', 3553280),
    ('PING-RISK-MG', 'BUY', 'SO54-20160703', 32.3974, 'EUR', 100000, 'THB', 3239740),
    ('PING-RISK-MG', 'SELL', 'SO55-20160703', 32.3974, 'EUR', 100000, 'THB', 3239740),
    ('PING-RISK-MG', 'BUY', 'SO56-20160703', 1.0968, 'EUR', 100000, 'USD', 109680),
    ('PING-RISK-MG', 'SELL', 'SO57-20160703', 0.1504, 'CNY', 2000000, 'USD', 300800)
;

CREATE TABLE MappingLegType(
    `TransactionType` varchar(4)
  , `ISCrossCurrency` varchar(1)
  , `BOTCode` int
  , `BOTValue` varchar(21)
  , `BOTDescription` varchar(56)
);
    
INSERT INTO MappingLegType(
    `TransactionType`
  , `ISCrossCurrency`
  , `BOTCode`
  , `BOTValue`
  , `BOTDescription`)
VALUES
    ('BUY', 'N', 182001, 'Buy-Foreign-Currency', 'นิติบุคคลรับอนุญาตซื้อเงินตราต่างประเทศแลกกับสกุลเงินบาท'),
    ('SELL', 'N', 182002, 'Sell-Foreign-Currency', 'นิติบุคคลรับอนุญาตขายเงินตราต่างประเทศแลกกับสกุลเงินบาท'),
    ('BUY', 'Y', 182003, 'Cross-Currency', 'การซื้อขายระหว่างเงินตราต่างประเทศ-2-สกุล'),
    ('SELL', 'Y', 182003, 'Cross-Currency', 'การซื้อขายระหว่างเงินตราต่างประเทศ-2-สกุล')
;

• ลองเขียน Query ครับ

SELECT ITX.PortfolioCode
     , ITX.TransactionType
     , ITX.FIArrangementNumber
     , LEGTYPE.BOTCode
     , LEGTYPE.BOTValue
     , ITX.CommodityCurrency
     , ITX.QuoteCurrency
     , ITX.ExchangeRate
     , ITX.CommodityAmount
     , ITX.QuoteAmount
FROM INVESTTX ITX
  LEFT OUTER JOIN MappingLegType LEGTYPE
    ON LEGTYPE.ISCrossCurrency = (
      CASE  WHEN ITX.CommodityCurrency = 'THB' OR ITX.QuoteCurrency = 'THB' THEN 'N' 
               ELSE 'Y'
         END
       )
       AND LEGTYPE.TransactionType = ITX.TransactionType

• สำหรับตัวอย่างผม ทำไว้ใน SQL Fiddle โดยผมได้ทดสอบบน MySQL
• สำหรับ MS SQL Server ผมได้ลองใน VM แล้วครับ หากใครอยากทดสอบลองแก้ Table และปรับ Syntax ดูครับ
• ส่วนอันนี้เป็น Query ที่ผมเขียนบน DB2 ครับ ยังไม่ Optimize อะไร

SELECT  ITX.PORTFOLIOID
      , P.PORTFOLIOCODE
      , P.PURPOSE                  AS PORTFOLIOTYPEID
      , PORTTYPE.NAME              AS PORTFOLIOTYPECODE
      , ITX.INVESTTXID
      , ITX.INVESTTXNO
      , ITX.SECURITYID
      , SEC.SECURITYCODE
      , UNDSEC.UNDERLYINGTYPECODE
      , INDEXTYPE.INDEXINVOLVEMENTTYPECODE
      , ITX.BROKERID
      , BR.BROKERCODE              AS BROKERCODE
      , BRCOM.DSET_ORGANIZATIONID  AS BROKERORGANIZATIONID
      , BRUNIQUEIDTYPE.CODE        AS BROKERUNIQUEIDTYPECODE
      , ITX.COUNTERPARTYID
      , CTP.COUNTERPARTYCODE       AS COUNTERPARTYCODE
      , CTPCOM.DSET_ORGANIZATIONID AS COUNTERPARTYORGANIZATIONID
      , CTPUNIQUEIDTYPE.CODE       AS COUNTERPARTYUNIQUEIDTYPECODE
      , EX.BOTFUTUREMARKETCODE     AS FUTUREMARKETCODE
      , EX.EXCHANGETYPE
      , ITX.TRADEDATE
      , SEC.MATURITYDATE
      , ITX.INVESTTXTYPEID
      , ITX.FUTURESPRICE
      , ITX.UNIT                   AS NOOFCONTRACT
      , FTA.MULTIPLIER 
      , ITX.EXCHANGERATE
      , ITX.CURRENCYID             AS COMMODITYCCYID
      , COMCCY.CURRENCYCODE        AS COMMODITYCCYCODE
      , FTA.QUOTED                 AS QUOTECCYID
      , QUOTECCY.CURRENCYCODE      AS QUOTECCYCODE
      , LEGTYPE.LEGTYPECODE        AS LEGTYPECODE
 FROM INVESTTX ITX
   LEFT OUTER JOIN PORTFOLIO P  
     ON ITX.PORTFOLIOID = P.PORTFOLIOID
       AND P.ACTIVEFLAG = 'A'
   --JOIN TO FIND PORTFOLIO TYPE 10010017
   LEFT OUTER JOIN SYSTEMLOOKUP PORTTYPE
     ON P.PURPOSE = PORTTYPE.ID
       AND PORTTYPE.LOOKUPCATEGORYID = 10010017
       AND PORTTYPE.ACTIVEFLAG = 'A'
   --JOIN TO FIND PORTFOLIO TYPE 10010017 
   LEFT OUTER JOIN SECURITY SEC  
     ON ITX.SECURITYID = SEC.SECURITYID
       AND SEC.ACTIVEFLAG = 'A'
   LEFT OUTER JOIN SECURITYTYPE ST
     ON SEC.SECURITYTYPEID = ST.SECURITYTYPEID
       AND ST.ACTIVEFLAG = 'A'
   LEFT OUTER JOIN INVESTMENT INV
     ON ST.INVESTMENTID = INV.INVESTMENTID
       AND INV.ACTIVEFLAG = 'A'
   --JOIN FUTURES MASTER
   LEFT OUTER JOIN FUTURES FTA
     ON SEC.SECURITYID = FTA.SECURITYID
       AND FTA.ACTIVEFLAG = 'A'
   LEFT OUTER JOIN UNDERLYINGSECURITY UNDSEC
     ON FTA.UNDERLYINGSECURITYID = UNDSEC.UNDERLYINGSECURITYID
       AND UNDSEC.ACTIVEFLAG = 'A'
   --JOIN FUTURES MASTER
   --JOIN BROKER DATA
   LEFT OUTER JOIN BROKER BR
     ON ITX.BROKERID = BR.COMPANYID
       AND BR.ACTIVEFLAG = 'A'
   LEFT OUTER JOIN COMPANY BRCOM 
     ON BR.COMPANYID = BRCOM.COMPANYID
       AND BRCOM.ACTIVEFLAG = 'A'
   --FIND UNIQUE ID TYPE 10026094
   LEFT OUTER JOIN SYSTEMLOOKUP BRUNIQUEIDTYPE
     ON BRCOM.DSET_INVOLVEDPARTYTYPEID = BRUNIQUEIDTYPE.ID
       AND BRUNIQUEIDTYPE.LOOKUPCATEGORYID = 10026094
       AND BRUNIQUEIDTYPE.ACTIVEFLAG = 'A'
   --FIND UNIQUE ID TYPE 10026094
   --JOIN BROKER DATA
   --JOIN COUNTER PARTY DATA
   LEFT OUTER JOIN COUNTERPARTY CTP  
     ON ITX.COUNTERPARTYID = CTP.COUNTERPARTYID
   LEFT OUTER JOIN COMPANY CTPCOM
     ON CTP.REFERENCEID = CTPCOM.COMPANYID
       AND CTPCOM.ACTIVEFLAG = 'A'
   --FIND UNIQUE ID TYPE 10026094
   LEFT OUTER JOIN SYSTEMLOOKUP CTPUNIQUEIDTYPE
     ON CTPCOM.DSET_INVOLVEDPARTYTYPEID = CTPUNIQUEIDTYPE.ID
       AND CTPUNIQUEIDTYPE.LOOKUPCATEGORYID = 10026094
       AND CTPUNIQUEIDTYPE.ACTIVEFLAG = 'A'
   --FIND UNIQUE ID TYPE 10026094
   --JOIN COUNTER PARTY DATA
   --JOIN EXCHANGE
   LEFT OUTER JOIN EXCHANGE EX
     ON FTA.EXCHANGEID = EX.EXCHANGEID
       AND EX.ACTIVEFLAG = 'A'
   --JOIN EXCHANGE
   --COMMODITY CURRENCY
   LEFT OUTER JOIN CURRENCY COMCCY
     ON ITX.CURRENCYID = COMCCY.CURRENCYID
       AND COMCCY.ACTIVEFLAG = 'A'
   --COMMODITY CURRENCY
   --QUOTE CURRENCY
   LEFT OUTER JOIN CURRENCY QUOTECCY
     ON FTA.QUOTED = QUOTECCY.CURRENCYID
       AND QUOTECCY.ACTIVEFLAG = 'A'
   --QUOTE CURRENCY
   --JOIN TO FIND INDEX INVOLVEMENT 
   LEFT OUTER JOIN DSET_MAPPARAM_INDEXINVOLVEMENTTYPE INDEXTYPE
     ON INV.INTERNALCODE = INDEXTYPE.INTERNALCODE
       AND UNDSEC.UNDERLYINGTYPECODE = INDEXTYPE.UNDERLYINGTYPECODE
       AND INDEXTYPE.DATASETTYPE = 'FTA'
       AND INDEXTYPE.ISENABLE = 'Y'
   --JOIN TO FIND INDEX INVOLVEMENT
   --JOIN TO FIND ARRANGEMENT BY PRODUCT-FUTURE
   LEFT OUTER JOIN DSET_MAPPARAM_ARRANGEMENTTYPE_FTA ARR_FTA
     ON INV.INTERNALCODE = ARR_FTA.INTERNALCODE
       AND UNDSEC.UNDERLYINGTYPECODE = ARR_FTA.UNDERLYINGTYPECODE
       AND ARR_FTA.ISENABLE = 'Y'
       AND ARR_FTA.DATASETTYPE = 'FTA'
   --JOIN TO FIND ARRANGEMENT BY PRODUCT-FUTURE
   --JOIN TO FIND ARRANGEMENT BY TX TYPE
   LEFT OUTER JOIN DSET_MAPPARAM_ARRANGEMENTTYPE_TX ARR_TX
     ON ITX.INVESTTXTYPEID = ARR_TX.TXTYPEID
       AND ARR_TX.ISENABLE = 'Y'
       AND ARR_TX.DATASETTYPE = 'FTA'
   --JOIN TO FIND ARRANGEMENT BY TX TYPE
   --JOIN TO FIND LEGTYPE 
   LEFT OUTER JOIN DSET_MAPPARAM_LEGTYPE LEGTYPE
     ON LEGTYPE.ISCROSSCURRENCY = (
         CASE  WHEN ITX.CURRENCYID = 1 OR FTA.QUOTED = 1 THEN 'N' 
               ELSE 'Y'
         END
       )
       AND LEGTYPE.TXARRANGEMENTTYPEID = ARR_TX.ARRANGEMENTTYPEID
       AND LEGTYPE.PRODUCTARRANGEMENTTYPEID = ARR_FTA.ARRANGEMENTTYPEID
       AND LEGTYPE.ISENABLE = 'Y'
       AND LEGTYPE.DATASETTYPE = 'FTA'
   --JOIN TO FIND LEGTYPE
 WHERE ITX.ISDELETED = 'N'
   AND ITX.ISCANCELLED = 'N'
   AND ITX.ISPOSTED = 'Y'
   AND ITX.TRADEDATE = '2016-02-01'
   AND ITX.PORTFOLIOID = 201
   AND INDEXTYPE.CONFIGID IN (SELECT TINDEXTYPE.CONFIGID
                              FROM DSET_MAPPARAM_INDEXINVOLVEMENTTYPE TINDEXTYPE
                              WHERE TINDEXTYPE.ISENABLE = 'Y'
                                AND TINDEXTYPE.DATASETTYPE = 'FTA'
                                AND INV.INTERNALCODE = TINDEXTYPE.INTERNALCODE
                                AND UNDSEC.UNDERLYINGTYPECODE = TINDEXTYPE.UNDERLYINGTYPECODE)

หมายเหตุ : ผมยังไม่ได้สนใจ Performance และอาจจะมีวิธีอื่นๆ ที่ทำได้ดีกว่านะครับ ที่เขียน Blog เพื่อไปเป็นแนวทาง สำหรับการไปประยุกต์ใช้ครับ ^__^