Re: Query to return results of search

Daniel Morgan wrote:
>
> 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

And you really should because your query has a bug, unless ALL of  those search columns include a NOT NULL constraint.

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com