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

    Unanswered: db2 challenge...im stumped

    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?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    Location
    Belgium
    Posts
    1,126
    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
    __http://www.abis.be/

  4. #4
    Join Date
    Apr 2009
    Posts
    2
    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
  •