| WHERE Clause For Multiple Select Bind Variable [message #309744] |
Fri, 28 March 2008 11:57 |
jshenn
Messages: 1
Registered: March 2008
|
Junior Member |
|
|
I am trying to create a portal report with several bind variables on a customization form. Three of the variables are bound to Combo Box LOV's. These work fine. Two of the variables are bound to Multiple Select LOV's. The first of these works fine because there are only three possible values and there is a reasonable default value. The second Multiple Select is giving me trouble.
In most cases, we want to select one or more values from the list. This works fine with the pl/sql below. In some cases, we want to select a specific :p_empname and return all of the associated :p_descr values. I have the % character in my :p_descr LOV and have set that as the default value, but this returns zero records for every :p_empname.
Here is the pl/sql I am using:
SELECT
c.location_descr,
c.deptid,
c.shift,
c.emp_name,
c.cert_lic_type,
c.cert_lic_descr,
c.dt_issued
FROM MS.MS_EMP_CERT_LIC_TRAIN c
WHERE
c.location LIKE NVL(:p_loc, '%')
AND c.deptid LIKE NVL(:p_deptid,'%')
AND c.emplid LIKE NVL(:p_empname,'%')
AND c.shift IN :p_shift
AND c.cert_lic_descr IN :p_descr
Is there a way to add conditional logic in the WHERE clause so that the % value can be passed correctly (using a LIKE operator)? Can this condition be handled with a different strategy?
[Updated on: Fri, 28 March 2008 12:55] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
