Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009

    Unanswered: db2 stumped


    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?

  2. #2
    Join Date
    May 2003
    I would use dynamic SQL to contruct the statement based on the parameters input.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2004
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  4. #4
    Join Date
    Apr 2009
    Thanks! the % worked just fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts