แก้ปัญหา multiple values sql IN : oracle

คำสั่ง IN เป็นคำสั่งที่ใช้สำหรับตรวจสอบว่า  ค่าที่เลือกมานั้น  มีอยู่ใน IN หรือไม่  เช่น

...
...
SELECT code
FROM ...
WHERE code IN('AAA', 'BBB', 'CCC')
...
...

ซึงเราสามารถตรวจสอบได้เพียง ครั้งละ 1 ค่าเท่านั้น (single value)
ในที่นี้ field code สามารถเป็น AAA หรือ BBB หรือ CCC ได้เท่านั้น (ถึงจะ match กัน)
ไม่สามารถเป็น multiple values เช่น 'AAA, BBB' หรือ 'BBB, CCC' ได้ เพราะมันจะไม่ตรงกับตัวไหนใน IN เลย

 'AAA, BBB' ไม่ match กับ 'AAA'
 'AAA, BBB' ไม่ match กับ 'BBB'
 'AAA, BBB' ไม่ match กับ 'CCC'

แล้วถ้าเราต้องการให้มัน match กันละ  เราจะทำยังไง?
เพราะตามความเป็นจริงแล้ว  'AAA, BBB' มันควรจะ match กับทั้ง  'AAA' และ 'BBB'

มาดูแนวคิด + วิธีแก้ของผมกันครับ

sql IN เป็นคำสั่งที่ใช้ข้อมูลในแต่ละ rows มา IN
ฉะนั้น  เราต้องแปลง 'AAA, BBB' ของเราให้ไปเป็น rows ก่อน  จากบทความนี้ครับ Split String to rows : Oracle SQL  ได้ผลลัพดังนี้

พอเราได้ rows มาแล้ว  เราก็สามารถใช้ sql IN ได้
แต่!  คำตอบที่ได้ออกมา  มันเป็น การลด rows ลงเฉยๆ

จริงๆ ผลลัพธ์ที่เราต้องการคือ ตอบว่า match หรือไม่ match เท่านั้น (ตอบเป็น boolean หรือ 1 กับ 0)
เราจึงครอบมันด้วยการ count  แล้วเช็คว่าถ้า count แล้วมากกว่า 0 ก็แสดงว่า match  (ตอบ 1) แต่ถ้าเท่ากับ 0 ก็คือไม่ match (ตอบ 0)

โอเค  แนวคิดนี้ใช้ได้  ท่าทางจะ work
แต่ก็ยังเจอปัญหาอยู่ดี

ตอนนี้ 'AAA, BBB, CCC' เราเป็น fixed code ถ้าเราต้องการส่งค่านี้มาจากการ select ข้างนอกล่ะ เราจะทำยังไง  คำตอบคือ  ไม่ได้  เพราะมันมี select count ครอบอยู่ 1 ชั้น  ที่กันไม่ให้ select ชั้นนอก  ส่ง field select เข้ามาได้

ชิปหายล่ะ  แนวคิดได้  แต่ดันตายที่ select count  กั้นอยู่  จะแก้ปัญหานี้ยังไง?
แนวทางการแก้ปัญหาของผมคือ

ทำยังไงก็ได้  ให้ select count มันอยู่ชั้นเดียวกับ 'AAA, BBB, CCC'

นั่งคิดอยู่สักพักนึงครับ   โอเค  ผมเกทล่ะ

แนวคิดเดิม  แต่เปลี่ยนกรรมวิธีใหม่
ใช้ procedure (PL/SQL) เข้ามาช่วย  รวบคำสั่ง split row to string ไปไว้ใน sql function ดังนี้
(define oracle pl/sql collection (object and table) types)

define type

CREATE OR REPLACE TYPE string_list AS TABLE OF varchar2(255);

write procedure

CREATE OR REPLACE FUNCTION split_string2list(p_string VARCHAR2) RETURN string_list AS
  l_list string_list := string_list();
BEGIN
  FOR rcd IN
  ( 
  SELECT DISTINCT str --reduce duplicate string
     FROM
     (
   SELECT trim(regexp_substr(p_string, '[^,]+', 1, LEVEL)) str
         FROM dual
         CONNECT BY LEVEL <= regexp_count(p_string, ',')+1
     )
     WHERE str IS NOT NULL
  )
  LOOP
    l_list.extend();
    l_list(l_list.last) := rcd.str;
  END LOOP;
  
  RETURN l_list;
  
  EXCEPTION WHEN OTHERS THEN
      RETURN NULL;

END split_string2list;

ลองเขียน code select ใหม่

ได้ล่ะ สั้นๆ  

ตอนนี้ไม่มี select count มาครอบมันล่ะ  เพราะ count อยู่ใน LEVEL เดียวกัน  โอเค  แก้ปัญหาได้ล่ะ

ต่อไป  เราก็สามารถส่ง select field จากข้างนอก  เข้ามา check ได้ว่า field นี้ match กับที่อยู่ใน IN หรือไม่  โดยสามารถส่งเข้ามาเป็น multiple values ใน column เดียวได้ครับ

หวังว่าบทความนี้จะเป็นประโยชน์สำหรับ developer ทุกคนที่เข้ามาอ่านน่ะครับ :)