I've got a search page thing going on in JSP (JSTL), and I want to be able to search on any of the fields that are entered, basically an AND filter. So as to not create dynamic SQL queries, I use the below SQL code:
<query part here.......>
and nvl(d.cable,' ') like nvl(?,'%') and
nvl( d.pair , ' ') like nvl(?,'%') and
nvl( to_char(d.prefix) , ' ') like nvl(?,'%') and
nvl( upper(d.lname) , ' ') like upper(nvl(?,'%')) and
nvl( upper(d.fname) , ' ') like upper(nvl(?,'%')) and
nvl( to_char(d.DN) , ' ') like nvl(?,'%') and
nvl( to_char(d.TN) , ' ') like nvl(?,'%') and
nvl( upper(d.DEPT_CD) , ' ') like nvl(?,'%')
<JSTL PARAMS>
sql:param value="${param.cable}"/>
<sql:param value="%${param.pair}%"/>
<sql:param value="${param.prefix}"/>
<sql:param value="%${param.lname}%"/>
<sql:param value="%${param.fname}%"/>
<sql:param value="%${param.dn}%"/>
<sql:param value="%${param.tn1}%${param.tn2}%${param.tn3}%${param.tn4}%"/> <sql:param value="${param.dept_cd}"/>
This works well, except all the 'like' statements really bog down the Oracle system. Can anyone suggest a way to dynamically create the above query portion, only putting in the statements that actually have data?
I don't know much Java, but this seems like an applicable language to create the text, but PL/SQL seems viable also. JSTL might be inappropriate for this. Any thoughts? Am I reinventing the wheel?
Thanks,
- Nic.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
