Query to return results of search

Hi

I've been asked to do the Oracle bit of a web application where there is a search screen that returns a set of results. The search criteria are optional, so that if nothing is entered for a field, no restriction on data is made for that field

The search criteria as follows:

Surname
id
Category
Low Amount, High Amount
Low Date, High Date
Postcode

Now my first cut of SQL is as follows ...

SELECT forename,

       surnames .... etc
  FROM az_data
 WHERE surname LIKE UPPER(p_surname)||'%'    AND id = NVL(p_id,id)
   AND cat_code = NVL(p_cat_code,cat_code)    AND amt BETWEEN NVL(p_low_amt,0) AND NVL(p_hi_amt,9999999999999)    AND req_date BETWEEN NVL(p_low_date,TO_DATE('20000101','YYYYMMDD')) AND NVL(p_hi_date,TO_DATE('29990101','YYYYMMDD'))

   AND postcode LIKE UPPER(p_postcode)||'%' ORDER BY surname, forenames;

Now I know it works, but I can't help feeling I ought to be taking another approach.

Any thoughts/ideas, anyone?

CE Received on Tue Jul 09 2002 - 09:58:37 CDT