check out this SQL statement

[peter moss]

I'm trying to make an advanced search page in JSP where the user can search based on multiple criteria...
But I want the user to be able to leave some of the criteria blank (i.e. under gender, if they went male and female results, then they just don't change the select box)
So I've done something like this, leaving the option value of the first/default selection blank in hopes that it will pass on a blank entry to the next page and when i select * from cases where gender = '', I want it to show all the males and females. BUT this isn't working.
out.print("<p>Gender:");
out.print("<select name=\"gender\">");
out.print("<option value =\"\"></option>");
out.print("<option value=\"Male\">Male</option>");
out.print("<option value=\"Female\">Female</option>");
out.print("</select><p>");

String query = "SELECT * FROM Cases WHERE Diagnosis = '" + diagnosis + "' AND Age > " + age1 + " AND Age < " + age2 + " AND Gender = '" + gender + "' AND Category = '" + category + "' AND Study = '" + study + "'";
I'm pretty new to this stuff so I'm probably just making a major but simple error. What is the best approach to building SQL search statements?

[Nagendra Prasad]

what you probably need is the ability to append the where clauses to the select statement programmatically.
In your example the condition "gender = null" would give u incorrect results. That is not desirable.
You need to append clauses based on whether the values are entered in the first place. Have an if/else conditional setup for the simplest illustration.
if value in param is null
append "gender is null" to where clause
else
append "gender = Parameter"

that should see u thru!

[Tom Blough]

You don't want to use

gender = null

in your while criteria. That would give you results for only those cases where the patiend was neither male or female. This will result in a very small result set
What you really want is

where gender in ("Male", "Female")

- this will return cases without regard to gender.

[Wayne L Johnson]

Better yet, if they don't select a gender [for instance], just omit the "And Gender = ..." from the clause entirely. Keep in mind that you must code around your specific database. Can the "Gender" field be null in the database? If so, then doing a "where Gender in ('Male', 'Female') will not return records where the gender is null.
What about case-sensitivity [eg. Sybase is normally case-insensitive, Oracle is case-sensitive]. Do you have entries like "MALE", "male", and "Male" in the database? If so, then you have to code for that.
Anyway, I'd do something like:
if (value in param is not null) then
append "And Gender = <param>"
end
Just keep in mind that the first clause needs to be "Where ...", and all subsequent clauses need to be "And ...".