Optimizing PL/SQL filter

Question

I'm not very familiar with PL/SQL but I spotted a bunch of code on a buddy's project that looks very wrong to me, could I get a better alternative?

SET filter_conditions = 
  '
  AND 
  (
    ((
      processes.get_main_GC_id(nov.GCID) IN ('''', ''01'',''02'',''03'',''04'',''05'',''07'',''08'',''09'',''10'',''12'',''13'',''14'',''16'')
      OR (processes.get_main_GC_id(nov.idGrupoCliente) IN (''23'', ''29'') AND ExtraNumber != 0)
      OR (processes.get_main_GC_id(nov.idGrupoCliente) IN (''28'',''35'',''36'',''37''))
      OR (processes.get_main_GC_id(nov.idGrupoCliente) IN (''40'') AND ExtraNumber != 0)
      OR (processes.get_main_GC_id(nov.idGrupoCliente) IN (''151'', ''152'', ''153''))
    ) AND Cortesia !=  ''S'')
    OR
    (Cortesia =  ''S'' AND cortesiaGrupo = 6 AND cortesiaCampania = 5 AND calle != '''')
  )
  ';

Particularly the ridiculous amount of OR-INs. I edited a lot of them out but trust me there were hundreds. Is there a way to use a vector or something and check whether the variable matches?

Looks like the two conditions with `AND ExtraNumber != 0` could be combined, as could the other two `processes.get_main_GC_id(nov.idGrupoCliente) IN ()` lists. What type does `processes.get_main_GC_id()` return? If it's numeric you could lose a ton of quotes. Also `= ''` and `!= ''` will never be true as @GreenT mentioned. The whole thing is a bit unstructured though, so it's hard to see any really systematic way of refactoring it. Also it looks like it's SQL (that is, a query) not PL/SQL (the programming language). — William Robertson, Nov 04 '18 at 18:25

score 0 · Answer 1 · answered Nov 03 '18 at 11:29

declare
  l_filter_conditions VARCHAR2(2000) := q'{
                                            AND (
                                                 (
                                                   (processes.get_main_GC_id(nov.GCID) IN ( '01','02','03','04','05','07','08','09','10','12','13','14','16') OR processes.get_main_GC_id(nov.GCID) is NULL)
                                                OR (processes.get_main_GC_id(nov.idGrupoCliente) IN ('23', '29','40') AND ExtraNumber != 0) 
                                                OR (processes.get_main_GC_id(nov.idGrupoCliente) IN ('28','35','36','37','151','152','153')) 
                                                ) 
                                                AND Cortesia !=  'S' OR (Cortesia,cortesiaGrupo,cortesiaCampania) =  (('S',6,5)) AND calle IS NOT NULL
                                                )
                                        }';
begin
DBMS_OUTPUT.PUT_LINE(l_filter_conditions); 
end;

Statement processed.

AND ( ( (processes.get_main_GC_id(nov.GCID) IN ( '01','02','03','04','05','07','08','09','10','12','13','14','16') OR processes.get_main_GC_id(nov.GCID) is NULL) OR (processes.get_main_GC_id(nov.idGrupoCliente) IN ('23', '29','40') AND ExtraNumber != 0) OR (processes.get_main_GC_id(nov.idGrupoCliente) IN ('28','35','36','37','151','152','153')) ) AND Cortesia != 'S' OR (Cortesia,cortesiaGrupo,cortesiaCampania) = (('S',6,5)) AND calle IS NOT NULL )

I don't know if this answers your question but i took myself some time to re-stylish your code.

You can store your string into a varchar2 variable and after that use it in your filter.
Use Q literal in case you have a long string with a lots of single quotes.(more info you will find here: https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html)
Use expression lists in case you have to many ANDs (https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm#i1033664)
I saw that in your code you check for an empty string value, in Oracle an empty string '' is treated as NULL if it's stored as a VARCHAR2 datatype (more info you can find here: Why does Oracle 9i treat an empty string as NULL?)

Optimizing PL/SQL filter

1 Answers1