I have data as below.

i am trying to bring this into below format.

col1=02 AND col2=001 OR col2=002 AND col3<>A AND col4<>test1 AND col4<>test2 AND col4<>test3

Could you please suggest oracle SELECT query.

Thanks in advance.

CodePudding user response：

If you want to check how Oracle interprets the predicate, simple create the query with the WHEREclause having your predicate.

The table with the corresponding columns must exists but may be empty.

Then call explain plan and look at the result - check the filter in Predicate Information

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
select * from tab
where 
col1 = 02 and
col2 in ('001','002') and
col3 <> 'A' and
col4 not in ('test1','test2','test3');

get result (cut for brevity)

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'));

...
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("COL1"=02 AND ("COL2"='001' OR "COL2"='002') AND 
              "COL3"<>'A' AND "COL4"<>'test1' AND "COL4"<>'test2' AND "COL4"<>'test3')

CodePudding user response：

You can write a function:

CREATE FUNCTION parse_filters
RETURN CLOB
IS
  v_sql  CLOB := EMPTY_CLOB();
  v_expr CLOB;
  c_in_value_pattern VARCHAR2(60)
    := '('
       || '\d \.\d*|\.\d '    -- Number literal
       || '|'
       || '''([^'']|'''')*''' -- String literal
       || '|'
       || '".*?"'             -- Quoted identifier
       || '|'
       || '[A-Z][A-Z0-9_$#]*' -- Identifier
       || '),?';

  PROCEDURE append_expr(
    p_sql           IN OUT CLOB,
    p_expr          IN     CLOB,
    p_oper          IN     VARCHAR2,
    p_wrap          IN     BOOLEAN DEFAULT FALSE
  )
  IS
    v_is_start CONSTANT BOOLEAN := LENGTH(p_sql) = 0;
  BEGIN
    IF NOT v_is_start THEN
      p_sql := p_sql || ' ';
    END IF;
    IF NOT v_is_start THEN
      p_sql := p_sql || p_oper || ' ';
    END IF;
    IF p_wrap THEN
      p_sql := p_sql || '(' || p_expr || ')';
    ELSE
      p_sql := p_sql || p_expr;
    END IF;
  END;
BEGIN
  FOR f IN (SELECT attribute, operator, value FROM filters)
  LOOP
    IF f.operator IN ('=', '<>', '!=') THEN
      v_expr := EMPTY_CLOB() || f.attribute || ' ' || f.operator || ' ' || f.value;
      append_expr(v_sql, v_expr, 'AND');
    ELSIF LOWER(f.operator) = 'in' THEN
      DECLARE
        v_list FILTERS.VALUE%TYPE := SUBSTR(f.value, 2, LENGTH(f.value) - 2);
        v_term FILTERS.VALUE%TYPE;
        v_idx  PLS_INTEGER := 1;
        v_temp CLOB;
      BEGIN
        v_expr := EMPTY_CLOB();
        LOOP
          v_term := REGEXP_SUBSTR(f.value, c_in_value_pattern, 1, v_idx, 'i', 1);
          EXIT WHEN v_term IS NULL;
          v_temp := EMPTY_CLOB() || f.attribute || ' = ' || v_term;
          append_expr(v_expr, v_temp, 'OR');
          v_idx := v_idx   1;
        END LOOP;
        append_expr(v_sql, v_expr, 'AND', TRUE);
      END;
    ELSIF LOWER(f.operator) = 'not in' THEN
      DECLARE
        v_list FILTERS.VALUE%TYPE := SUBSTR(f.value, 2, LENGTH(f.value) - 2);
        v_term FILTERS.VALUE%TYPE;
        v_idx  PLS_INTEGER := 1;
      BEGIN
        LOOP
          v_term := REGEXP_SUBSTR(f.value, c_in_value_pattern, 1, v_idx, 'i', 1);
          EXIT WHEN v_term IS NULL;
          v_expr := EMPTY_CLOB() || f.attribute || ' <> ' || v_term;
          append_expr(v_sql, v_expr, 'AND');
          v_idx := v_idx   1;
        END LOOP;
      END;
    END IF;
  END LOOP;
  RETURN v_sql;
END parse_filters;
/

For your sample data:

CREATE TABLE filters ( Attribute, operator, value ) AS
SELECT 'col1', '=',      '''02''' FROM DUAL UNION ALL
SELECT 'col2', 'In',     '(''001'',''002'')' FROM DUAL UNION ALL
SELECT 'col3', '<>',     '''A''' FROM DUAL UNION ALL
SELECT 'col4', 'Not In', '(''test1'',''test2'',''test3'')' FROM DUAL;

Then:

SELECT parse_filters() FROM DUAL;

Outputs:

PARSE_FILTERS()
col1 = '02' AND (col2 = '001' OR col2 = '002') AND col3 <> 'A' AND col4 <> 'test1' AND col4 <> 'test2' AND col4 <> 'test3'

db<>fiddle here