Re: SQL select via logicals

[Buck Calabro <mcalabro@xxxxxxxxxxxx>]

>Thanks for the test. I hope I didn't come across too testy (so to speak)
>when I threw down that gauntlet. Must be this cold.

Not at all!  Like I said, I was happy to do it... this sort of thing is always
an education...

[The question is this:  If I issue and SQL request against a logical]
[and the logical is built with select/omit criteria, will the records]
[returned be filtered by the s/o criteria?]

I ran some tests:
>>
>>OK.  Here's the set-up:
>>Physical file MASTER keyed by PEXC, PNUM, PSUF.  Record count 13,390.
>>Logical file BILLPRT keyed by ZIP, BTEXC, BTNUM, BTSUF, PEXC, PNUM, PSUF
>>                   SELECT TYPE NE 'C'
>>
>>If I run this statement: select count(*) from master
>>I get this answer: 13,390  
>>
>>If I run this statement: select count(*) from billprt
>>I get this answer: 13,322
>>
>>If I run this statement: select count(*) from billprt where type = 'C'
>>I get this answer: 0
>>
>>In all these interactive statements, I get the following message in debug:
>>Arrival sequence access was used for file xxxxxx
>>
>>I ran this one: select * from billprt order by pexc,pnum,psuf
>>Got this count: 13,322 (I scrolled to the end of the list)
>>Got this message:    Access path built for file BILLPRT.
>>
>>On my machine at V4R1, SQL used the logical I specified, and it obeyed
>>the select criteria in use.  If there's another set of circumstances you'd 
>>like me to test with, I'd be very happy to do it: I would have been
>>shocked to the core if I did a SELECT from the logical and got all the
>>records, under any circumstances!
>
>Looks like it had to create a new access path, since the ORDER BY you used
>is deep into the logical's key.
>
>What results do you get if you specify the WHERE clause with the physical,
>setting the same criteria as the S/O above?
>
>select * from master where type <> 'C' order by pexc,pnum,psuf

Just what'd you'd expect: 13,322 records (same as the S/O logical)

The optimiser timed out, and built an access path even though the 
physical file if already keyed the same as the ORDER BY.

Buck Calabro
Commsoft

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---