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?

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

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.