20211014mgh
Posted by Anonymous on Thu 14th Oct 2021 09:29
raw | new post



CREATE TABLE T AS

SELECT 'PD01' STK_ID,'SERIE_NO' FIELD,'=' RELATION,'07' F_VALUE ,'J002' branch_no FROM dual
UNION ALL
SELECT 'PG01' STK_ID,'SERIE_NO' FIELD,'=' RELATION,'07' F_VALUE ,'J002' branch_no FROM dual
UNION ALL
SELECT 'PD01' STK_ID,'SERIE_NO' FIELD,'<>' RELATION,'07' F_VALUE ,'J001' branch_no FROM dual
UNION ALL
SELECT 'PG01' STK_ID,'SERIE_NO' FIELD,'<>' RELATION,'07' F_VALUE ,'J001' branch_no FROM dual;


DECLARE
  V_P VARCHAR2(200) := 'PD01\06';

  V_P1    VARCHAR2(200);
  V_P2    VARCHAR2(200);
  V_VALUE VARCHAR2(200);
BEGIN
  V_P1 := REGEXP_SUBSTR(V_P, '[^\]+', 1, 1);
  V_P2 := REGEXP_SUBSTR(V_P, '[^\]+', 1, 2);

  EXECUTE IMMEDIATE

  '
SELECT F_VALUE FROM T
WHERE STK_ID=''' || V_P1 || '''
AND  (
(T.RELATION = ''='' AND F_VALUE = '''||v_p2||''')
OR
(T.RELATION = ''<>'' AND F_VALUE <> '''||v_p2||''')
)
'
    INTO V_VALUE;


  DBMS_OUTPUT.PUT_LINE (V_VALUE);
END;


SELECT * FROM T
WHERE STK_ID='PD01'
AND  (
(T.RELATION = '=' AND F_VALUE = '06')
OR
(T.RELATION = '<>' AND F_VALUE <> '06')
)