Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    6

    Unanswered: Using Host Variable to Compare with Literal

    We ran across SQL that compared a host-variable against a literal value in a Where clause in a COBOL program (part of cursor declaration). It came in the form of the following:

    ColumnA = :ws-host-var1 and
    ColumnB = :ws-host-var2 and
    ColumnC = :ws-host-var3 or :ws-host-var3 = ' '

    My question is how this will evaluate (particularly for ColumnC)? I can find no examples of this kind of predicate, and one article that says any comparison of a host variable to a literal is a Stage 2 predicate.

    What are the rules of comparing host variables to a literal in a where clause?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It would be a stage 2 predicate and ColumnC would not be elligible for matching index access. You'll run into those types of SQL from time to time. In fact, I just ran into one last month, that I asked the developer to move into cobol prior to executing the SQL, so that we could get index access.
    (CASE :WS-HV1
    WHEN 'xxx' THEN '06'
    WHEN 'yyy' THEN '06'
    ELSE :WS-HV2 END) = A.column1
    They could very easily determine if they want to use the value 06 prior to executing the query and move 06 to their host variable, WS-HV2. They would use an IF/ELSE then the SQL would look like:
    :WS-HV2 END = A.column1

    I could swear there used to be something in the application programming and SQL guide, though I can't find it at present. Its not even mentioned in Table 88. (Predicate types and processing). I'm sure that several of the leading writers have written about it at some point or another.

    Dave Nance

  3. #3
    Join Date
    Oct 2007
    Posts
    6
    The one thing I found was a reference to a comparison between host variable and literal as being Stage 2 in an article by Bonnie Baker.

Posting Permissions

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