DB2 - handling columns defined as null

Let's say you have three textboxes that you can use to search for data. Each textbox will correspond to a column on the DB2 table. The search string you enter will be inserted into the where clause. For example, you have First-Name, Last-Name, and Phone Number. If you don't enter data into a particular textbox, I default its value in the where clause to '_', the wildcard character to select everything. Also, lets say Phone Number is defined as NULL on the table.

Cursor1 will be used if the user has entered a Phone number to search for. So the where clause will look something like this:

Where FIRST_NAME like :firstname AND LAST_NAME like :lastname AND PHONE_NBR like :number

This works when data is entered for phone number. But if a search is done for First Name only, the cursor returns partial or no results because the :number host variable will be populated with the "_' wildcard. PHONE_NBR like '_' will only return the rows that have a real value. If there is a null for PHONE_NBR on a row that matches the First Name you searched开发者_如何学编程 for, that row won't show up. So I created a second cursor.

Cursor2 will be used if the user HAS NOT entered a Phone number to search for. The Where clause looks something like this.

Where FIRST_NAME like :firstname AND LAST_NAME like :lastname AND (PHONE_NBR like :number OR PHONE_NBR IS NULL)

So again, if a search was done for a first name only, and some values in PHONE_NBR have data, some are null, EVERYTHING that matches the first name that is searched for will show in the results - which is good. For the rows with values in PHONE_NBR, PHONE_NBR like '_' will get those. For the rows with null in PHONE_NBR, PHONE_NBR IS NULL will get those.

This is a minor yet necessary difference. Because of this minor difference, I would like to combine these two cursors into one. How can that be done to achieve the same results?

Ian, i think the difference is if the user supplies a number he doesn't want to return rows with a null. using cursor 2 all the time would return rows with null along with matching numbers.

You could try a CASE statement based on :number...though i'm not sure if you can use a CASE with "is null" syntax. i know you could if you were just checking for different values (equal to, less than, etc).

The way I'd recommend handling this is by building the query to supply conditions only on the columns where the user enters data. That is:

• If the user enters something in the First_Name text box, you have a condition such as:

  FIRST_NAME LIKE '...'
  

• If the user enters something in the Last_Name text box, you have a condition such as:

  LAST_NAME LIKE '...'
  

• If the user enters something in the Phone_Nbr text box, you have a condition such as:

  PHONE_NBR LIKE '...'
  

In each case, the 3 dots represent a string derived from the information typed into the text box, and the function that does that conversion is fully aware of quoting (to avoid SQL injection).

If the user types in two or three of the text boxes, the independent conditions are joined by an AND. If the user types nothing, you can generate a tautology such as 1 = 1 as the condition.

You then append that condition to the WHERE clause of the SQL statement, and then arrange to execute it.

This is the technique made available by the CONSTRUCT statement in IBM Informix 4GL; it has been available there since 1986. It allows for conditions other than just LIKE, such as equals, less than, greater than or equal to, ranges, or even a list of alternatives (for an IN ('val1', 'val2', ...) condition), and it can be used for all data types.