[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 ครับ
  

  Code	Value	Description
  182001	Buy Foreign Currency	นิติบุคคลรับอนุญาตซื้อเงินตราต่างประเทศแลกกับสกุลเงินบาท
  182002	Sell Foreign Currency	นิติบุคคลรับอนุญาตขายเงินตราต่างประเทศแลกกับสกุลเงินบาท
  182003	Cross Currency	การซื้อขายระหว่างเงินตราต่างประเทศ 2 สกุล

• ข้อมูล Transaction
  

  Portfolio Code	Transaction Type	FI Arrangement Number	Exchange Rate	Commodity Currency	Commodity Amount	Quote Currency	Quote Amount
  PING-RISK-MG	BUY	SO52-20160703	35.5328	THB	3,553,280	USD	100,000
  PING-RISK-MG	SELL	SO53-20160703	35.5328	USD	100,000	THB	3,553,280
  PING-RISK-MG	BUY	SO54-20160703	32.3974	EUR	100,000	THB	3,239,740
  PING-RISK-MG	SELL	SO55-20160703	32.3974	EUR	100,000	THB	3,239,740
  PING-RISK-MG	BUY	SO56-20160703	1.0968	EUR	100,000	USD	109,680
  PING-RISK-MG	SELL	SO57-20160703	0.1504	CNY	2,000,000	USD	300,800

• ออกแบบ 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 เพื่อไปเป็นแนวทาง สำหรับการไปประยุกต์ใช้ครับ ^__^