READ PREVIOUS/PRIOR in cobol

[sunilm...@gmail.com]

Hi All,

I am planning to place the pointer in the file to the key values passed from cics screen and read previous 15 records i.e (key<= values) for the display to the user screen.

I tried with START but it doesn't allow a 'less than' keyword.

Move high values and read prior will start reading from the last record of the file which is not what I want.

Can someone help me with this?

Thanks in advance!

Regards,
Sunil.

[CRPence]

On 31-Aug-2015 20:18 -0600, sunilm...@gmail.com wrote:
> I am planning to place the pointer in the file to the key values

> passed from CICS screen and read previous 15 records i.e (key<=

> values) for the display to the user screen.
>
> I tried with START but it doesn't allow a 'less than' keyword.
>
> Move high values and read prior will start reading from the last
> record of the file which is not what I want.
>
> Can someone help me with this?

The IBM i ILE COBOL apparently lives in /Bizzaro-World/ per use of
some effective inverse-logic to accomplish the task. Essentially the
effect is available, but depends on the ASCEND versus DESCEND attribute
of the key, from which the READ requests are directed.

A READ PRIOR from the intended START key position of [the unsupported
less-than predicate] (KEY<=values) against an ASCENDing key sequence
would be the effective equivalent of a READ NEXT from that same START
key position of [the supported greater-than predicate] (KEY>=values) for
the DESCENDing key sequence. The key-positioning request via START is
just to gain position, and the READ PRIOR or READ NEXT is what gets the
actual data; so by simply accepting the oddity [apparently the ANSI
COBOL] of the capability restricted to EQUAL, GREATER THAN, and GREATER
THAN OR EQUAL, just code the opposite direction for the READ with the
opposite collation [e.g. DESCEND vs ASCEND] while using the same key
values, and the expected results should be achieved.

An example follows, one that I think might elucidate, showing how the
same key value but both the opposite key-sequence and opposite
READ-direction used in two scenarios below will achieve the same results
[ignoring that what I offer seems to contradict the docs for the "START
GREATER", for which a link and a complete quotation are included further
below]:

START key>=1.5 for the following ASCENDing key data gets position for
the value=1.7 so two READ...PRIOR requests get the two /prior/ values of
1.4 and 1.1 and in that order.

1.1
1.4
1.7
2.0

START key>=1.5 for the following DESCENDing key data gets position
for the value=1.7 so two READ...NEXT requests get the /next/ values of
1.4 and 1.1 and in that order.

2.0
1.7
1.4
1.1

[http://www.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_72/rzase/cbldbfdesckey.htm]
_Processing Files with Descending Key Sequences_
"Files created with a descending keyed sequence (in DDS) cause the READ
statement NEXT, PRIOR, FIRST, and LAST phrases to work in a fashion
exactly opposite that of a file with an ascending key sequence. You can
specify a descending key sequence in the DDS with the DESCEND keyword in
positions 45 to 80 beside a key field. In descending key sequence, the
data is arranged in order from the highest value of the key field to the
lowest value of the key field.

For example, READ FIRST retrieves the record with the highest key value,
and READ LAST retrieves the record with the lowest key value. READ NEXT
retrieves the record with the next lower key value. Files with a
descending key sequence also cause the START qualifiers to work in the
opposite manner. For example, START GREATER THAN positions the current
record pointer to a record with a key less than the current key.

Parent topic: _File Processing Methods for DISK and DATABASE Files_
[http://www.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_72/rzase/cbldbfprocess.htm]"

--
Regards, Chuck

[Jonathan Ball]

On 10/13/2015 8:29 PM, CRPence wrote:
> On 31-Aug-2015 20:18 -0600, sunilm...@gmail.com wrote:
>> I am planning to place the pointer in the file to the key values
>> passed from CICS screen and read previous 15 records i.e (key<=
>> values) for the display to the user screen.
>>
>> I tried with START but it doesn't allow a 'less than' keyword.
>>
>> Move high values and read prior will start reading from the last
>> record of the file which is not what I want.
>>
>> Can someone help me with this?
>
> The IBM i ILE COBOL apparently lives in /Bizzaro-World/ per use of
> some effective inverse-logic to accomplish the task. Essentially the
> effect is available, but depends on the ASCEND versus DESCEND attribute
> of the key, from which the READ requests are directed.

With an ascending key file, using START <file-name> KEY IS >=
<key-value> followed by READ PRIOR [up to] 15 times will do exactly what
is needed.

Suppose you have a numeric key and there are 15 rows with key values
1-15, then the next key value is 21. The user enters a value of 20 in
the display screen. START <file-name> KEY IS >= <key-value> where the
value is 20 will position the file at the row with a value of 21. The
program then does READ PRIOR 15 times, reading rows with key values of
15, 14, 13, ... , 1 - exactly the desired result. This is exactly the
same as would be done in RPGLE with SETLL/READP.

Personally, unless there were a shop standard against it, I would do
this in SQL using a cursor with an ORDER BY <key-column> DESC clause:

exec sql
declare get_15 cursor for
select [column list]
from <file-name>
where <key-column> < :cics-entered-value-variable
order by <key-column> desc
end-exec

Open the cursor, fetch a row, start a PERFORM UNTIL loop to process and
re-fetch 15 times or until SQLSTATE = '02000'.

[Jonathan Ball]

I responded to Chuck Pence's reply to your inquiry, but I probably
should have replied directly to your post.

You don't need a "less than" functionality to the START statement. You
can get exactly what you want with "greater than or equal to".

Elaborating some on the reply I made to Chuck's post, suppose your table
has a single numeric key column (let's say data type is INTEGER) and
your user enters a key value of 40 into the screen. Assume there are at
least 15 rows in the table with a key value less than 40, and that there
is a row with a key of 39. Assume as well that there are rows with a
key value >= 40. Then:

START <file-name> KEY IS >= CICS-ENTRY-VALUE

will position the file to the row with the first key value that is
greater than or equal to 40 (it doesn't matter if there is a row with
the value 40 or not.) Then a READ PRIOR will read the row with a key
value of 39, and 14 more instances of READ PRIOR will read the remaining
rows (or, you'll reach your AT END condition earlier.)

As I said in my earlier reply, I would probably do the data access using
SQL unless the company has a policy forbidding it. You could declare
and open a cursor using an ORDER BY with a descending key, then do a
single multiple-row FETCH for 15 rows into your host structure array.
The field SQLERRD(3) in the SQLCA will tell you how many rows were
actually fetched into your table.

[CRPence]

On 14-Oct-2015 22:58 -0600, Jonathan Ball wrote:
> On 10/13/2015 8:29 PM, CRPence wrote:
>> On 31-Aug-2015 20:18 -0600, sunilm...@gmail.com wrote:
>>>
>>> I am planning to place the pointer in the file to the key values
>>> passed from CICS screen and read previous 15 records i.e (key<=
>>> values) for the display to the user screen.
>>>
>>> I tried with START but it doesn't allow a 'less than' keyword.
>>>

>>> <<SNIP>>

>>
>> The IBM i ILE COBOL apparently lives in /Bizzaro-World/ per use of
>> some effective inverse-logic to accomplish the task. Essentially
>> the effect is available, but depends on the ASCEND versus DESCEND
>> attribute of the key, from which the READ requests are directed.
>>
>
> With an ascending key file, using START <file-name> KEY IS >=
> <key-value> followed by READ PRIOR [up to] 15 times will do exactly
> what is needed.
>
> Suppose you have a numeric key and there are 15 rows with key values
> 1-15, then the next key value is 21. The user enters a value of 20
> in the display screen. START <file-name> KEY IS >= <key-value>
> where the value is 20 will position the file at the row with a value
> of 21. The program then does READ PRIOR 15 times, reading rows with
> key values of 15, 14, 13, ... , 1 - exactly the desired result.

> <<SNIP>>
>
>> <<SNIP>>
>>

Yet the OP seems to have requested that a read previous would start
from *less than* the-key-value [though in pseudo-code, was instead
expressed as *less than or equal to* the-key-value]. So it seems that
if the input was 20 with that same data-example, then a row positioning
request for start-key<=20 presumably should yield the row having the
key-value 15, and therefore only the prior 14 rows could be read
previous through the ascending key until an effective EOF, thus reading
rows with key values of 14, 13, ... , 1 as the intended output.?

And FWiW, I tested with the DESCEND key using *greater than* and READ
NEXT [on v5r3], and seemingly according to the results noted, if the
input was 20 with that same data-example, then a row-positioning request
for start-key>=20 apparently yielded the row having the key-value 21,
and the next 15 rows could be read forward\next through the descending
key, reading rows with key values of 15, 14, 13, ... , 1 as _a
contradiction to_ the intended output.?

Yet FWiW, the docs [snipped from quoted reply] seem to suggest that
with the DESCEND key using *greater than* and READ NEXT, if the input
was 20 with that same data-example, then a row-positioning request for
start-key>=20 should yield the row having the key-value 15, and
therefore only the next 14 rows could be read forward\next through the
descending key until an effective EOF, thus reading rows with key values
of 14, 13, ... , 1 as the intended output.? Note: that the
start-key>=20 should yield the row with value 15 instead of the row with
value 21 is that for which I expressed as a /bizarro world/ effect,
because while I recognize that value of 15 is _further beyond_ as in the
_next_ key value from the key-value of 20, at least according to the
DESCEND attribute, that the logical expression (15>=20) is clearly false
is difficult to overlook [knowing that the logical expression (21>=20)
is clearly true], so the [as-expressed by the docs to be a valid] result
seems bizarre.

--
Regards, Chuck

[Jonathan Ball]

It seems to me the OP was asking for the first 15 rows (in descending
order, assuming an ascending key) lower than the entered key value. If
there were *either* START...LESS THAN or START...LESS THAN OR EQUAL TO
options, assuming they were available, it would become complicated,
because he then could not unambiguously use a READ PRIOR - he might
conceivably skip a row that should be included. By using
START...GREATER THAN OR EQUAL TO, as I suggested, he *unambiguously*
gets the first row of the desired set, assuming there are any rows at
all that have a key value lower than the entered value.

>
> And FWiW, I tested with the DESCEND key using *greater than* and READ
> NEXT [on v5r3], and seemingly according to the results noted, if the
> input was 20 with that same data-example, then a row-positioning request
> for start-key>=20 apparently yielded the row having the key-value 21,
> and the next 15 rows could be read forward\next through the descending
> key, reading rows with key values of 15, 14, 13, ... , 1 as _a
> contradiction to_ the intended output.?

If you have a table with a descending key and do a START...GREATER THAN
OR EQUAL TO and then do a READ NEXT, getting the correct first row
depends on whether or not there is a row with a key exactly equal to the
entered value. Suppose the user enters the value 20 and wants rows with
a key value less than 20. If there's a row with a key value of 20, then
the READ NEXT (assuming a descending key) will get the next row with a
value less than 20. But if there's no row with a key value of 20, then
the READ NEXT will read the *second* row with a key value less than 20 -
that is, your first row read might have a key of 18 (or 17 or 16), even
though there is a key value of 19 that you want.

>
> Yet FWiW, the docs [snipped from quoted reply] seem to suggest that
> with the DESCEND key using *greater than* and READ NEXT, if the input
> was 20 with that same data-example, then a row-positioning request for
> start-key>=20 should yield the row having the key-value 15, and
> therefore only the next 14 rows could be read forward\next through the
> descending key until an effective EOF, thus reading rows with key values
> of 14, 13, ... , 1 as the intended output.?

Yes, exactly - but the row with a key value of 15 should be included,
and here it will be excluded.

[CRPence]

On 15-Oct-2015 01:32 -0600, Jonathan Ball wrote:
> It seems to me the OP was asking for the first 15 rows (in
> descending order, assuming an ascending key) lower than the entered
> key value.

I had originally contemplated the same, after a cursory review of the
OP; that the author was just overlooking the obvious.

But then the conspicuousness of the scenario being resolved so
easily, merely by specifying the supported (start_key>=values) made that
original contemplation seem implausible. The implication of requiring
the effect of [and the OP's apparent lamenting the lack of support for]
a (start_key<=values) predicate, had me convinced that there must be
something for which the requested capability of the
less-than[-or-equal-to] predicate must be imperative to the resolution
of the given scenario.

Thus I figured, so-readily dismissing what was apparently the stated
desire by the OP, to achieve the effect of a LESS THAN [OR EQUAL TO]
test on the START, seemed perhaps a bit presumptuous.

> If there were *either* START...LESS THAN or START...LESS THAN OR
> EQUAL TO options, assuming they were available, it would become
> complicated, because he then could not unambiguously use a READ PRIOR
> - he might conceivably skip a row that should be included. By using
> START...GREATER THAN OR EQUAL TO, as I suggested, he *unambiguously*
> gets the first row of the desired set, assuming there are any rows at
> all that have a key value lower than the entered value.

What is alluded as potentially a "complicated" effect, might not be.
The desired effect could be little different than asking for the list
of descending values less-than an effective FLOOR() of the input_value.
So for anyone unfamiliar with the intent of the OP, we might presume
that a row logically "should be included", but instead that row might
legitimately be expected to have been *excluded* from the results.
Possibly the /calculation/ of that effective FLOOR(input_value) might
exist only as the effect of having skipped\excluded a row, rather than
being a result that can be computed with an expression; thus no ability
to effect what might be expressed as (startkey>=floor(input_value))

Based on prior experiences replying to /old/ messages [and I hope my
doing so is not viewed as necro-posting ;-)]... I suppose probably our
followup replies have been mostly for naught anyhow, as Sunilkumar is
probably long gone, off onto other tasks since originally posting over
six weeks ago. I only responded so late to the OP, because purposefully
I had left that Post marked as unread, until I could respond both when I
had more time to do so and after I had a chance to dig-out and revise an
old ILE COBOL source with which to test [albeit sadly, finding in my
test on v5r3, that the results of the program did not match the
expected-results according to the docs]. If there is a followup by the
OP, hopefully then, some actual example(s) will be provided for the
input and the expected output, helping to clarify what are the actual
requirements.

--
Regards, Chuck