[SQL] 절에 가변적 목록과 테이블에서 SELECT

절에 가변적 목록과 테이블에서 SELECT

내가 프로젝트에서 문제에 직면하고있다을 일하고 것은, 나는 당신에게 실제 코드를 제공 할 수 있지만 아래로 실행 샘플 코드를 만들었습니다

여기에 온도 및 temp_id은 두 개의 테이블이 있습니다

임시 테이블에서 ID의 목록을 분리 나는 쉼표에서 ID를 얻어서 temp_id 테이블에서 행을 검색 할

//DDLs to create table
CREATE TABLE temp(ids VARCHAR2(4000));
CREATE TABLE temp_id(data_id NUMBER);

//DMLs to populate test data
INSERT INTO temp VALUES('1, 2, 3');

INSERT INTO temp_id VALUES(1);
INSERT INTO temp_id VALUES(2);
INSERT INTO temp_id VALUES(3);
INSERT INTO temp_id VALUES(4);
INSERT INTO temp_id VALUES(5);

이 쿼리는 작동하지 않습니다

SELECT * FROM temp_id WHERE data_id IN (SELECT to_number(COLUMN_VALUE) FROM XMLTABLE(SELECT ids FROM temp));

작업 쿼리

SELECT * FROM temp_id WHERE data_id IN (SELECT to_number(COLUMN_VALUE) FROM XMLTABLE('1, 2, 3'));

여기에 두 개의 쿼리 위의 차이는 내가 첫 번째 쿼리에서 임시 테이블에서 컬럼을 사용하고 두 번째 쿼리에서 인용 VARCHAR2을 지시하고있다. 왜 작동하지 않는 이유를 점점하지? 나는 뭔가를 놓치고 있습니까? 나는 그것을 알아낼 수 일부 데이터 형식이 일치하지만이있을 수 있습니다 생각합니다.

해결법

1. ==============================

   1.귀하의 요구 사항은 변하는 IN-리스트라고한다. WHERE 절에있는 값의 가변적 목록을 참조하십시오

   귀하의 요구 사항은 변하는 IN-리스트라고한다. WHERE 절에있는 값의 가변적 목록을 참조하십시오

   이유 : IN ( '1, 2, 3')이 (1, 2, 3) OR NOT IN과 동일하다 ( '1', '2', '3')

   그 후,

   와 동일

   오류 ORA-01722를 던진 것이다 : 유효하지 않은 숫자 -

   SQL> SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');
   SELECT * FROM temp_id WHERE data_id IN('1, 2, 3')
                                          *
   ERROR at line 1:
   ORA-01722: invalid number
   
   
   SQL> SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);
   SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp)
                                                 *
   ERROR at line 1:
   ORA-01722: invalid number
   

   동일하지

   이는 당신에게 정확한 출력을 줄 것이다 -

   SQL> SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);
   
      DATA_ID
   ----------
            1
            2
            3
   

   해결책 :

   귀하의 요구를 들어, 다음과 같이 그것을 달성 할 수 -

   SQL> SELECT * FROM temp;
   
   IDS
   --------------------------------------------------------------
   1, 2, 3
   
   SQL> SELECT * FROM temp_id;
   
      DATA_ID
   ----------
            1
            2
            3
            4
            5
   
   SQL> WITH data AS
     2    (SELECT to_number(trim(regexp_substr(ids, '[^,]+', 1, LEVEL))) ids
     3    FROM temp
     4      CONNECT BY instr(ids, ',', 1, LEVEL - 1) > 0
     5    )
     6  SELECT * FROM temp_id WHERE data_id IN
     7    (SELECT ids FROM data
     8    )
     9  /
   
      DATA_ID
   ----------
            1
            2
            3
   

   또는이를 달성하기 위해 자신의 TABLE 함수 또는 파이프 라인 기능을 만들 수 있습니다. 당신의 목표는 여러 행에 쉼표로 구분 IN 목록을 분할해야한다. 어떻게 당신이 당신에게 달려있다!

   작업 데모

   의는 SCOTT 스키마의 표준 EMP 테이블의 예를 보자.

   나는 문자열에서 작업 목록을 가지고 있고, 나는 그 작업의 직원을 계산하려면 :

   SQL> SET serveroutput ON
   SQL> DECLARE
     2    str VARCHAR2(100);
     3    cnt NUMBER;
     4  BEGIN
     5    str := q'[CLERK,SALESMAN,ANALYST]';
     6    SELECT COUNT(*) INTO cnt FROM emp WHERE JOB IN (str);
     7    dbms_output.put_line('The total count is '||cnt);
     8  END;
     9  /
   The total count is 0
   
   PL/SQL procedure successfully completed.
   

   오! 어떻게 된 거예요? 표준 EMP 테이블 이유는 그 VARYING IN의리스트 인 출력 (10)을 주어야한다.

   의 올바른 방법을 보자 :

   SQL> SET serveroutput ON
   SQL> DECLARE
     2    str VARCHAR2(100);
     3    cnt NUMBER;
     4  BEGIN
     5    str := q'[CLERK,SALESMAN,ANALYST]';
     6    SELECT COUNT(*)
     7    INTO cnt
     8    FROM emp
     9    WHERE job IN
    10      (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL))
    11      FROM dual
    12        CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
    13      );
    14    dbms_output.put_line('The total count is '||cnt);
    15  END;
    16  /
   The total count is 10
   
   PL/SQL procedure successfully completed.
   

2. ==============================

   2.이 즉 LIKE를 사용하여 수행 할 수있는 또 다른 방법이있다 :

   이 즉 LIKE를 사용하여 수행 할 수있는 또 다른 방법이있다 :

   SELECT ti.*
     FROM temp t, temp_id ti
    WHERE ',' || REPLACE(t.ids, ' ') || ',' LIKE '%,' || TO_CHAR(ti.data_id) || ',%'
   

   (필자는 ID 목록에서 불필요한 공백을 제거하는 위) (교체 사용 - 약간 간단한 일을한다.) 또는, 하나는 REGEXP_LIKE를 (사용할 수 있습니다)

   SELECT ti.*
     FROM temp t, temp_id ti
    WHERE REGEXP_LIKE(REPLACE(t.ids, ' '), '(^|,)' || TO_CHAR(ti.data_id) || '(,|$)')
   

   [캐럿 (^) 및 달러 기호 ($) 문자는 문자열의 각각 시작과 끝을 일치합니다 - ID 중 하나를 문자열의 시작에 뭔가를 일치시킬 수 있도록 (쉼표 또는 마지막으로 끝나는 문자열) 또는 뭔가 쉼표 나 문자열의 끝)로 끝나는, 다시 (쉼표로 시작하는.]

from https://stackoverflow.com/questions/27738359/select-from-table-with-varying-in-list-in-where-clause by cc-by-sa and MIT license