RE: Imbedded SQL and dynamic variables

Is this function operating by documented design? I've perused the

SQL

manuals on this, but cannot seem to find a definitive description.

I don't know if it's documented but the observed behaviour is what I
would expect. An access plan is created based on the evaluated SQL
statement before any data is retrieved. That plan does not change as a
result of any fetched data. However, once that SQL statement completes
then if you rerun it it will use the current value of the host
variable and different records will be retrieved.

Simon, I hear you (and Michael) on this. And I can see it, but here's where
I'm coming from:

If I have a table where all the possible integer values have been inserted
into COL1 and I do:
SELECT VAL1 FROM MYTABLE WHERE VAL1 BETWEEN 1 AND VAL1 + 10
I would expect to return all rows. The "VAL1+10" value will be evaluated
each time. So I get hung up when imbedded SQL doesn't (seem to) do the same
thing. I get it: the program variable ( :VAL1 in my prior example ) is
treated sort of like a constant (whose life is the span from OPEN to CLOSE)
in the statement's perspective. I know that if we'd wanted dynamic we could
have omitted the colon (and, in my example, changed the name). But it seems
counterintuitive. Maybe it's just me.

(I use this dynamic comparator capability a lot when looking for missing
numbers in sequences (for example), which is why it caught my eye. I like
the way that works and I want to keep it. :))

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
Flashlight: (n) A case for holding dead batteries.