How to take where clause conditions from table column in oracle SQL or plsql-CodePudding

How to take where clause conditions from table column in oracle plsql.

E.g. data in table Condition 1.sourceSystemId = 'SN' 2.AND(coverageType='AD',amountType1='PREMIUM',premiumFrequency='REGULAR',yearOfPremium='1')

e.g query: select * from xyz where rule='abc' and "sourceSystemId = 'SN'"

select * from xyz where rule='abc' AND(coverageType='AD',amountType1='PREMIUM',premiumFrequency='REGULAR',yearOfPremium='1')

CodePudding user response：

Not entirely sure what you're asking here, but I would imagine that

select * from xyz where rule='abc' AND(coverageType='AD',amountType1='PREMIUM',premiumFrequency='REGULAR',yearOfPremium='1')

would become

select * from xyz 
where rule='abc' 
AND coverageType='AD' 
and amountType1='PREMIUM' 
and premiumFrequency='REGULAR' 
and yearOfPremium='1'

CodePudding user response：

I suppose you want something like :

DECLARE 
  l_query  VARCHAR2(2000) := 'select * from xyz where rule=''abc''';
  l_result xyz%ROWTYPE;
  l_cursor SYS_REFCURSOR;
BEGIN 
  dbms_output.put_line(l_query);
  FOR clause IN (SELECT condition
                   FROM conditions)
  LOOP
    l_query := l_query||' AND '||clause.condition;
  END LOOP;
  
  OPEN l_cursor FOR l_query;
    LOOP
      FETCH l_cursor INTO l_result;
      EXIT WHEN l_cursor%NOTFOUND;
        ..
      -- your processing
    END LOOP;
  CLOSE l_cursor;
END;

CodePudding user response：

Here is example of SQL solution. I used justt first and last condition but you can get them all...

WITH
    xyz As
        (
            Select 1 "ID", 'abc' "RULE", 'AD' "COVERAGETYPE", 'PREMIUM' "AMOUNTTYPE1", 'REGULAR' "PREMIUMFREQUENCY", '1' "YEAROFPREMIUM" From Dual
          UNION
            Select 2 "ID", 'abc' "RULE", 'BF' "COVERAGETYPE", 'ORDINARY' "AMOUNTTYPE1", 'EXTRA' "PREMIUMFREQUENCY", '2' "YEAROFPREMIUM" From Dual
          UNION
            Select 3 "ID", 'abc' "RULE", 'AD' "COVERAGETYPE", 'PREMIUM' "AMOUNTTYPE1", 'REGULAR' "PREMIUMFREQUENCY", '1' "YEAROFPREMIUM" From Dual
        ),
    conditions As
        (
            SELECT UPPER('coverageType=AD,amountType1=PREMIUM,premiumFrequency=REGULAR,yearOfPremium=1') "CND" From Dual
        )
SELECT
    x.ID, x.RULE, x.COVERAGETYPE, x.AMOUNTTYPE1, x.PREMIUMFREQUENCY, x.YEAROFPREMIUM
FROM
    xyz x 
INNER JOIN
    conditions c ON(1=1)
WHERE
    x.RULE = 'abc' And
    x.COVERAGETYPE =  CASE WHEN InStr(c.CND || ',', 'COVERAGETYPE=') = 0 THEN x.COVERAGETYPE
                      ELSE SubStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'COVERAGETYPE=')   Length('COVERAGETYPE=')), 1, InStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'COVERAGETYPE=')   Length('COVERAGETYPE=')   1), ',')) END And
    x.YEAROFPREMIUM = CASE WHEN InStr(c.CND || ',', 'YEAROFPREMIUM=') = 0 THEN x.YEAROFPREMIUM
                      ELSE SubStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'YEAROFPREMIUM=')   Length('YEAROFPREMIUM=')), 1, InStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'YEAROFPREMIUM=')   Length('YEAROFPREMIUM=')   1), ',')) END

Result:

    ID RULE COVERAGETYPE AMOUNTTYPE1 PREMIUMFREQUENCY YEAROFPREMIUM
     1 abc  AD           PREMIUM     REGULAR          1            
     3 abc  AD           PREMIUM     REGULAR          1