Re: SQL weirdness: can anybody explain this malfunction scenario with a customer's file?

James

A view should return the same thing as if you ran the encaspsulated SELECT statement in interactive SQL. Order is not assured, of course. If this is truly not happening, that's clearly a bug.

BTW, it does not matter at all how the PF (table) was created. At least not if all is working correctly.

Vern

James H. H. Lampert wrote:

Can anybody explain why this would happen under V5R4:

A customer's physical file, FOO, in library FROBOZZ. Don't know whether it was created with a CRTPF or in SQL.

It has an unkeyed logical file that was created as an SQL view:
CREATE VIEW FOOV2 AS SELECT FOO01, FOO02, FOO03, FOO04, FOO05,
FOO06, FOO07, FOO08, FOO09, FOO10, FOO11, FOOBAR FROM
FROBOZZ.FOO WHERE FOOBAR <> 'HISTORY' RCDFMT FOOREC

This should produce a bunch of records that meet the where-clause. Thousands, in fact.

It seems that if you look at the logical using native record-level access, though, you initially get one record that meets the where-clause, followed by a bunch that don't. Unless you open the file in DFU first. Then, after perhaps some intermittent recurrence of the problem until you sign off and sign back on, it behaves as intended.

Then, if you remove all of the logicals, and the physicals, from the library, and restore them from the save file from whence they came, expecting the malfunction to return, it STILL behaves correctly.

(The file, library, and field names have been changed to protect the innocent.)

--
JHHL