How to combine IN and LIKE

[JA3395]

I have a problem I can't resolve

I have a parameter that can be either contain '%' or 'VALUE' or 'VALUE,VALUE,VALUE'

I need to code the select statement to cater for all of these possibilities

I currently have the following

     AND  NVL(SGBSTDN_RATE_CODE, '%') [b]IN[/b]  -- (lv_rate_code_list)
		   (SELECT DISTINCT gjbprun_value
		    FROM   gjbprun
		    WHERE  gjbprun_job = 'SZRAMGR'
		    AND  gjbprun_one_up_no = 50524
		    AND  gjbprun_number = '06')  -- this returns the value of the parameter

which works fine for a value but not for '%'.

How do I code an optional LIKE clause with the above statement?

 

[JA3395]

I've found a way, but the next problem is to parameter(ize) the values

CURSOR c_rate (lcur_oneupno number, lrc_Param varchar2, lsl_Param varchar2)  IS
   SELECT * FROM   SPRIDEN, SGBSTDN, STVSTYP
   WHERE  SGBSTDN_TERM_CODE_EFF = :sel_term_code
     AND EXISTS (
                 SELECT * 
                 FROM (
                       SELECT '%' AS Pattern FROM DUAL 
                       UNION ALL SELECT 'SELF%' FROM DUAL 
                       UNION ALL SELECT 'SPONS%' FROM DUAL
                      )
                 WHERE NVL(SGBSTDN_RATE_CODE, '%') LIKE Pattern)

I need to somehow pass lrc_Param to become

SELECT '%' AS Pattern FROM DUAL 
UNION ALL SELECT 'SELF%' FROM DUAL 
UNION ALL SELECT 'SPONS%' FROM DUAL

this bit as a parameter or split a parameter

 

[JA3395]

OK I solved it.

CURSOR c_rate (lcur_oneupno number, lrc_Param varchar2, lsl_Param varchar2)  IS
   SELECT * FROM   SPRIDEN, SGBSTDN, STVSTYP
   WHERE  SGBSTDN_TERM_CODE_EFF = :sel_term_code
     AND  NVL(SGBSTDN_RATE_CODE, '%') IN  -- (lv_rate_code_list)
          (SELECT DISTINCT 
                  CASE 
                    WHEN (gjbprun_value = '%') THEN
                      STVRATE_CODE            
                    ELSE
                      gjbprun_value
                  END CASE
             FROM gjbprun, 
                  stvrate
            WHERE gjbprun_job = 'SZRAMGR'
              AND gjbprun_one_up_no = TO_NUMBER(:one_up_no)
              AND gjbprun_number = '06'
              AND (gjbprun_value = STVRATE_CODE 
                  OR gjbprun_value = '%'))