Home » Fusion Middleware & Colab Suite » WebCenter Suite & Portal » WHERE Clause For Multiple Select Bind Variable (Oracle AS, 10.1.2, Windows 2003)
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

Previous Topic: webpage portal backup
Next Topic: Portal Application Development Tutorial
Goto Forum:
  


Current Time: Thu Mar 28 11:28:16 CDT 2024