Hi.

I have a cobol program in which a user can enter up to 5 different fields and the result will simply be the information from the table that matches input. The user can enter data in all 5 fields, or any combination/number of fields. For instance, I can enter in Name, Street, ZIP, city, state, or i can enter in just Name and state. Right now I just OR all of these conditions in one cursor and put default values into the fields that are left empty, but this does not give the desired result, it will still give all data.

My only other solution is to make an individual cursor for each possible combination, and then open it depending on whether or not the input variables but this seems like too much overhead.

Is there some way a cursor can detect which fields are empty and ignore them? How can I just do a search for Name and state leaving the other fields blank if I wanted to?

I would use dynamic SQL to contruct the statement based on the parameters input.

An other alternative is to set the host variables for the not-filled-in fields to "%"
(Do this inside the COBOL program: IF val1 = SPACE MOVE '%' TO val1.)
Then replace the "=" in the query by "LIKE".
In terms of performance, though, this *could* be worse than dynamic SQL. All depends on the size of the table(s), and on the way the fields are entered by the user. Actually, the performance could be much better than dynamic sql since you avoid the dynamic BIND step at each execution.
You'll probably also have to remove trailing blanks from all host variables (i.e., use "varchar" host variables), or replace them by "%"s, to make this solution working.

Thanks! the % worked just fine.