Re: Query to return results of search

Charlie Edwards wrote:

> 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

You should. And that approach is native dynamic SQL.

Daniel Morgan Received on Tue Jul 09 2002 - 10:05:48 CDT