Re: D3 Indices

Hi Mark
I have tested the variations ad nauseam. My initial dictionary is as per
the manual - knowing my dislike of algebraic over F correlatives I RTFM with
care (using Briz brilliant PEHELP not that wretched Update) and that is the
way they say to do it so obviously indexing is out of line with Access.
Unfortunately the index as you suggested appears to work but does not
although the Sorts & Lists do. I have established that it is storing
everything under the index 000 and thus one just gets a simple list of the
invoices and lines back with KEY. Trying to address 052 group through the
index just fails.

You asked for examples of more complex issues which I give below. I fully
agree with your using indexes as I use them extensively, It is just that I
roll my own and feel that perhaps I should be able to use Pick items
although I find the use of complex dictionaries abhorrent. I am surprised
that call statements are used by Scott for example. I am alos considering
writing my own workaround by putting out the string I want to sort on as
attribute 1 of an item in a "key" file with any old number as the key to the
item. In fact where I want 2 or 3 indices I could use attribute 2 and 3 etc
and just ask Pick to index the single attribute. Space is not at a premium
these days and I do remember in the late '80s it was considered ideal
practise to have meaningless numeric keys on items with all valid data
contained within the item - An attitude I totally disagree with.

Thanks for your help.
Peter McMurray

Here are some examples. The first two are on the stock movement file and
all the information is contained within the stock movement record.
Enquiries cannot wait for a full sort of the file. An algebraic dictionary
is going to be a mess. More importantly the second will only exist for
Stock Purchases and Returns.

STKMVEBT1*1
001 Stock Movements By Product & Pack - Btree
002
003 STKMVEBT1F
004 KEY
005 CO "R%2"
006 PRODUCT "R%6"
007 PACK "R%8"
008 DEPOT "L#4"
009 LOCCODE "L#4"
010 PERNO "R%7"
011 MVETYPE "R%2"
012 MVEDAT "R%5"
013 SOURCECO "R%2"
014 BATCHNO "R%6"
015 BLINE "R%3"
016 BATCHTYPE "L#1"

:

STKMVEBT2*1
001 Stock Movements From Suppliers - Btree
002
003 STKMVEBT2F
004 KEY
005 MVESOURCE "L#8"
006 MVEDOC "R%8"
007 SOURCECO "R%2"
008 DEPOT "L#4"
009 BATCHNO "R%6"
010 BLINE "R%2"

:
This combines Name information from the Debtors master file, which controls
credit, with Address information from the Address file which controls
pricing and delivery information. The Aboriginal centre may have deliveries
to 40 or 50 settlements, the Seventh Day Adventists may have deliveries to a
dozen of their pastors, ColesMyer could have 700 to 800 delivery points.
NAMEBT*1
001 The Name Enquiry Btree File
002
003 NAMEBTF
004 KEY
005 SORTNAME "L#10"
006 CO "R%2"
007 NUMBER "R%6"
008 ADDNO "R%3"

This allows us to check delivery requests by street. Dodgy debtors often
come in with different names but the same delivery address - it is not much
good having your heating oil delivered to somone else's tank.

STREETBT*1
001 The Street Enquiry Btree File

002
003 STREETBTF
004 KEY
005 SORTSTREET "L#10"
006 ADDNUM "R%5"
007 CO "R%2"
008 NUMBER "R%6"
009 ADDNO "R%3"

:
"Mark Brown" <mbrown@xxxxxxxxxxxxx> wrote in message
news:v60Ag.8218$Vq1.4667@xxxxxxxxxxxxxxxxxxxxxxx

"Peter McMurray" <excalibur21@xxxxxxxxxxx> wrote in message
news:4sZzg.5215$rP1.4602@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Mark
Thanks for the rapid response ( DO you work all night?) Unfortunately
the suggested correlative does not work in SORT BY-EXP as my original
did. Nor does the new method bring back a sorted list with say SORT
INVOICES BY-EXP GRPDESC "052]" it just comes back with no items present.
In trying to look at the key I could be wrong with my code but using the
'N' option to race through all the keys it appears to have just made a
list of all the invoice lines unsorted.

Here's my test file. Only three records and not much data

:list test pd
Page 1 test 04:20:56 02 Aug 2006

test...... Prod Desc.....................

2 003product 3
3 001product 1
003product 3
1 002product 2
001product 1

[405] 3 items listed out of 3 items.
:sort test pd by pd
Page 1 test 04:21:18 02 Aug 2006

test...... Prod Desc.....................

3 001product 1
003product 3
1 002product 2
001product 1
2 003product 3

[4051] 3 items listed.
:sort test pd by-exp pd
Page 1 test 04:21:22 02 Aug 2006

test...... Prod Desc.....................

1 001product 1
3 001product 1
1 002product 2
2 003product 3
3 003product 3

[4051] 5 items listed.

My a-corellative was

a1(TPROD;X;3;3)(MR%3):1(TPROD;X;2;2)

I'm running 7.4.4.400


If I take into account the enormously messy way I have to actually
reproduce the English correlative in Basic and I would typically have
upto 9 or 10 attributes in a btree key. I am wondering if it is
worthwhile on anything but the most simplistic sorts.

I'd like to see something like that. Usually I find we tend to
over-complicate things.

By the way I meant no disrespect to you. In fact I have fond memories of
the best presentation ever given over a couple of days was yours on
Microsoft interfacing. I don't remember who it was that answered my
original query but they certainly avoided complex keys that I
specifically asked about. I certainly have a better understanding of
what Pick is doing now from your post and it is not what I was expecting.
Perhaps you could volunteer a complex English correlative that I could
work through

Don't worry (be happy). I get plenty of disrespect, so I never feel short
changed.

How about: aa (correl: tprod;x;3;3) and bb (tprod;x;2;2) and then you can

an(aa)(mr%3):n(bb)


As for Btrees I have been using them for nigh on 25 years as Micromax had
a brilliant implementation and I sold the first of the Micromax 3000's of
the convention floor in 1982. The B-trees worked so well the client ran
around telling everybody how his new system was 10 times faster than his
direct IBM mainframe link to BP with the same data (we actually picked up
25,000 customers from the mainframe tapes so the data was very similar).

Regards
Peter McMurray

I've been working on B-trees sinced they were still called balanced trees,
when there were a fixed number of indexes per node and that was it. You
designed your tree with 1 block at the top, a second level with as many
blocks as there were keys in the first block, etc until you ended up with
the last layer which held one key:id pair for each record in the file.
Theoretically, it never took more than levels + 1 read to get to any
record.

BTW, I use indexed a LOT in Visual Basic and DOT Net. It's much faster to
read an index, put the display key into a pull-down or listbox and store
the item id than to sselect the file and read every record to get the same
information.

Mark Brown




.

Relevant Pages

  • Re: D3 Indices
    ... all the information is contained within the stock movement record. ... Enquiries cannot wait for a full sort of the file. ... 001 Stock Movements By Product & Pack - Btree ... controls pricing and delivery information. ...
    (comp.databases.pick)
  • Re: D3 Indices
    ... The problem is the NT FSI specific indexing. ... Enquiries cannot wait for a full sort of the file. ... 001 Stock Movements By Product & Pack - Btree ... controls pricing and delivery information. ...
    (comp.databases.pick)
  • Re: D3 Indices
    ... my test account is a VME account where indexing actually works. ... Enquiries cannot wait for a full sort of the file. ... 001 Stock Movements By Product & Pack - Btree ... controls pricing and delivery information. ...
    (comp.databases.pick)
  • Re: D3 Indices
    ... multi-line TCL statement and each statement is separated by the value mark. ... Enquiries cannot wait for a full sort of the file. ... 001 Stock Movements By Product & Pack - Btree ... controls pricing and delivery information. ...
    (comp.databases.pick)
  • Re: D3 Indices
    ... does the new method bring back a sorted list with say SORT INVOICES BY-EXP ... prefer F correlatives but popped in an "A" type for the exercise. ... the English pre-compiler looks at the WITH selection list ...
    (comp.databases.pick)