Oracle with case. It depends on the parameter

Question

Hi, I want to do the procedure as follows:

CREATE OR REPLACE PROCEDURE SOL.INSERT_LD_NEXTPROCESS (vgroupid NUMBER)
IS
VPERIODID     VARCHAR2 (10);
vPROCSESSID   NUMBER;

CURSOR c
IS
  SELECT COMPANYID,
         GROUPID,
         PERIODID,
         FN_PPROCESSCURRENT
FROM LIQUIDATIONSDETAILS
   WHERE     PROCESSID = FN_PPROCESSPREVIOUS
         AND (UNCOLLECTED > 0 OR INVOICE = 0)

I want to add an additional filter, which depends on the parameter:

CASE WHEN vgroupid > -1 then 
        AND GROUPID = vgroupid
ELSE
        NULL
END
...

therefore the reason where like

WHERE PROCESSID = FN_PPROCESSPREVIOUS
AND (UNCOLLECTED > 0 OR INVOICE = 0) AND GROUPID = vgroupid

when vgroupid = -1, then I need all entries a, when vgroupid> -1, then I only need entries in vgroupid

any idea?

Answer 1

CURSOR c
IS
  SELECT COMPANYID,
         GROUPID,
         PERIODID,
         FN_PPROCESSCURRENT
FROM LIQUIDATIONSDETAILS
   WHERE     PROCESSID = FN_PPROCESSPREVIOUS
         AND (UNCOLLECTED > 0 OR INVOICE = 0)
         AND (((GROUPID = vgroupid) AND (vgroupid > -1)) OR (vgroupid = -1))

for example: if vgroupid = -1, then the last condition will be (((GROUPID = -1) AND (-1 > -1)) OR (-1 = -1))or ((forever_false AND forever_false) OR (forever_true))or (-1 = -1)- all entries

instead, if the vgroupid = 123last condition is (((GROUPID = 123) AND (123 > -1)) OR (123 = -1))or (((GROUPID = 123) and forever_true) OR (forever_false))or (GROUPID = 123)- only 123 GROUPID