A few weeks ago I had posed a question about how one my create a cursor for
a query where the predicate condition is dynamic.  Meaning that the query
might want to have one of several possible predicates.  Take the following
queries, for instance:

-- check for branch/account and amount
SELECT BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT, SERIAL_NBR, SEQUENCE_NBR,
POST_FLAG
 FROM FILM.FILM_TRANSACTIONS
 WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND AMOUNT = 25.00;

-- check for branch/account and serial number
SELECT BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT, SERIAL_NBR, SEQUENCE_NBR,
POST_FLAG
 FROM FILM.FILM_TRANSACTIONS
 WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND SERIAL_NBR = 0;

-- check for branch/account and both amount and serial number
SELECT BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT, SERIAL_NBR, SEQUENCE_NBR,
POST_FLAG
 FROM FILM.FILM_TRANSACTIONS
 WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND AMOUNT = 25.00 AND
SERIAL_NBR = 1670;

Using dynamic SQL and building the predicate programatically depending on
the input is one option, but I was really looking at a way to do it using
static SQL.  Why?  Well, because that's what I wanted!  :-)

Anyway, I was reading the DB2 Server for VSE & VM Application Programming
manual (of all things!) and just happened to stumble on the possibility of
using input indicator variables in the predicate.  Here's what the manual
says:
----------------------
...there are cases where setting up a negative input indicator
variable in the predicate can prove useful and efficient. For example, if
an
application prompts the user to interactively supply information that will
identify an employee (by either number or name), you can design the program
to use only one select-statement to extract the indicated employee data from
the
database.
Here is the pseudocode:
   get either empno or lastname from user
   if empno is entered then empnoind = 0, else empnoind = -1
   if lastname is entered then nameind = 0, else nameind = -1
   SELECT * FROM EMPLOYEE
   WHERE EMPNO = :EMPNO:EMPNOIND
   OR LASTNAME = :NAME:NAMEIND
----------------------

This appears to be exactly what I'm looking for.  So I wrote a little
program to test it, and lo and behold it appears to work!  I am posting this
for a few reasons:
1) To see if I appear to be using it correctly (it appears to work, but
perhaps for some other reason?).
2) To see if there are comments on perhaps why I should not use this kind of
coding.  (Too be honest, if I had not read this and I had seen a SELECT like
I have below in my example I would never have figured out what it is
doing.)
3) To let others who don't know about it that this seemingly quite useful
feature is available.
4) To see if anyone has any other comments on my use of imbedded SQL.
(Ideas to make it better, etc.)
5) To see if this is documented anywhere in the DB2 LUW manuals.  My test
program is actually using DB2 LUW, but DB2 Server for VSE, but I can't find
this feature documented anywhere in the LUW manuals.

Anyway, here is my (COBOL) program:

      program-id. filmqry.

      environment division.
      configuration section.
      special-names.
          console is console.

      data division.
      working-storage section.
      copy "sqlenv.cbl".
      copy "sql.cbl".
      copy "sqlca.cbl".

      exec sql begin declare section end-exec.
      01  film-transactions.
          05  ft-brch-nbr             pic S9(3) comp-3.
          05  ft-acct-nbr             pic S9(7) comp-3.
          05  ft-post-date            pic x(10).
          05  ft-amount               pic S9(9)v99 comp-3.
          05  ft-serial-nbr           pic S9(9) comp-3.
          05  ft-sequence-nbr         pic S9(9) comp-3.
          05  ft-post-flag            pic x.
      01  indicators.
          05  ind                     pic s9(4) comp occurs 10.
      exec sql end declare section end-exec.

      77  errloc                    pic x(80).
      01  account-in.
          05  brch-in                 pic 9(3).
          05  acct-in                 pic 9(7).
      77  amount-in                   pic x(12).
      77  serial-in                   pic x(9).
      77  record-status               pic x.
          88  record-found                value 'Y'.
          88  record-not-found            value 'N'.
      01  search-flags.
          05  search-amount-flag          pic 9.
              88  search-amount               value 'Y'.
          05  search-serial-flag          pic 9.
              88  search-serial               value 'Y'.

      exec sql declare ft_select cursor for
          select BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT,
              SERIAL_NBR, SEQUENCE_NBR, POST_FLAG
          from FILM.FILM_TRANSACTIONS
          where BRCH_NBR = :ft-brch-nbr
                and
                ACCT_NBR = :ft-acct-nbr
                and
                (
                    AMOUNT = :ft-amount:ind(1)
                    or
                    SERIAL_NBR = :ft-serial-nbr:ind(2)
                    or
                    (
                        AMOUNT = :ft-amount:ind(3)
                        and
                        SERIAL_NBR = :ft-serial-nbr:ind(4)
                    )
                )
      end-exec.

      procedure division.
          perform connect
          perform get-account
          perform get-input
          perform until amount-in = spaces
                    and serial-in = spaces
              perform mainline
              perform get-input
          end-perform
          perform disconnect
          exit program.

      get-account.
          accept account-in from command-line
          move brch-in to ft-brch-nbr
          move acct-in to ft-acct-nbr
          .

      get-input.
          display 'amount to search' upon console
          accept amount-in from console
          display 'serial to search' upon console
          accept serial-in from console
          .

      mainline.
          perform set-search-fields
          perform start-query
          perform next-record
          if record-found
              display '**results found**'
          else
              display '**no results found**'
          end-if
          perform until record-not-found
              if record-found
                  perform display-record
              end-if
              perform next-record
          end-perform
          perform end-query
          display ' '
          .

      set-search-fields.
          initialize search-flags
          move -1 to ind(1), ind(2), ind(3), ind(4)
         
          if amount-in > spaces
              set search-amount to true
              move function numval(amount-in) to ft-amount
          end-if
          if serial-in > spaces
              set search-serial to true
              move function numval(serial-in) to ft-serial-nbr
          end-if

          move -1 to ind(1), ind(2), ind(3), ind(4)
          evaluate true
          when search-amount and search-serial
              display 'amount and serial'
              move 0 to ind(3), ind(4)
          when search-amount
              display 'amount only'
              move 0 to ind(1)
          when search-serial
              display 'serial only'
              move 0 to ind(2)
          when other
              display '**unexpected search**'
          end-evaluate
          .

      display-record.
          display ft-brch-nbr ' ' ft-acct-nbr ' '
                  ft-post-date ' ' ft-amount ' '
                  ft-serial-nbr ' ' ft-sequence-nbr ' '
                  ft-post-flag
          .

      connect.
          exec sql connect
              to testdb
              user xyz
              using ********
          end-exec
          call "checkerr" using SQLCA errloc
          .

      start-query.
          display ft-amount ' ' ft-serial-nbr
          display ind(1) ' ' ind(2) ' ' ind(3) ' ' ind(4)
          exec sql open ft_select
          end-exec
          call "checkerr" using SQLCA errloc
          .

      next-record.
          exec sql fetch ft_select
              into :film-transactions
          end-exec
          evaluate sqlcode
          when zero
              set record-found to true
          when 100
              set record-not-found to true
          when other
              call "checkerr" using SQLCA errloc                          

          end-evaluate
          .

      end-query.
          exec sql close ft_select
          end-exec
          call "checkerr" using SQLCA errloc
          .

      disconnect.
          exec sql connect reset
          end-exec
          call "checkerr" using SQLCA errloc                              
 
          .

      end program filmqry.

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO  USA