Dennis,
You declare a SQL. This is a set of instructions telling DB2 what you
want. It must be analyzed when the cursor is opened, to determine the
access plan (how is DB2 going to return what you asked for).
Now DB2 can go an build a result set. The selection of records matches
what you asked for..
Once a result set is generated, you FETCH rows of data out of the
resultant result set. The statement is NOT evaluated again until the
NEXT time you open the cursor.
Now, consider the SQL statement you list below...
SELECT VAL1 FROM MYTABLE WHERE VAL1 BETWEEN 1 AND VAL1 + 10
You are right, that every row of the file will be selected (regardless
of the values contained in VAL1). Why? Because your statement says
"retrieve all rows from my table where VAL1 is between 1 and itself +
10. If VAL1 = 1, then for sure VAL1 is between 1 and 11. If VAL1 =
1000, then for sure VAL1 is between 1 and 1010. Then only rows that
would ever filter out would be where VAL1 < 1 (zero or negative values).
Much better in my opinion to write this as
SELECT VAL1 FROM MYTABLE WHERE VAL1 > 0
Hth,
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Tuesday, April 27, 2010 6:24 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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.
As an Amazon Associate we earn from qualifying purchases.