Chris:
Indexes aren't created by default. The @ID is hashed into the file
which allows you to retrieve an item via the @ID with a single disk read.
To index, you need to create a dictionary. I create all our index
dictionaries as "INDEX_{n}" (e.g. INDEX_1, INDEX_2, etc). They're
I-Descriptors like:
5 Allegis (0)-> AE DICT ARTLEDGER INDEX_1
Top of "INDEX_1" in "DICT ARTLEDGER", 9 lines, 434 characters.
*--: P
001: I This is the INDEX dictionary that determines what the index is
for. Structure:
Client# Cust# YrMo Ctrl#
002: OCONV( CLIENTNO, 'MR%4' ) : OCONV( UNITNO, 'MR%7' ) : YRMO_PSTD :
OCONV( INVNO, 'MR%7' )
003:
004:
005: 24L
006: S
007:
As you can see they simply build a string that will be indexed by the
CREATE.INDEX command at TCL. It looks like:
5 Dev (0)-> LIST.INDEX ARTMASTER
Alternate Key Index Details for File ARTMASTER Page 1
File.................. ARTMASTER
Alternate key length.. 45
Node/Block size....... 4K
OV blocks............. 1 (1 in use, 0 overflowed)
Indices............... 3 (0 D-type)
Index updates......... Enabled, No updates pending
Index-Name...... F-type K-type Built Empties Dups In-DICT S/M
F-no/VF-expr....
INDEX_1 V Txt Yes Yes Yes Yes M SUBR(
'-CATS', S
UBR(
"T.LFM", NI
D,
@FILE.NAME, @
RECORD
), REUSE(
OCONV(
*OCONV(
@ID,
'G1*1' )*,
'MR%7' ) ) )
.
.
Then you have to execute the BUILD.INDEX command from TCL to populate
the index. Here's some quick and dirty BASIC code that manages an
index. First you need to open the file that's indexed.
*
*** Open files
FileErr = NULL$
OPEN '', 'ARTLEDGER' TO ARTLEDGER ELSE FileErr := ' ARTLEDGER'
IF FileErr NE NULL$ THEN
HLDMSG = "Missing files: " : FileErr
GOTO ABORT.PROGRAM
END
Then you need to define the index variable...
** Read index codes from ARTLEDGER file
ARL.INDEX.1 = "INDEX_1"
A.CODE = ARL.INDEX.1
Then you need to use the index.
** Read transaction pointers
DONE = 0
TextLine = NULL$
TOP.START = CLIENT "R(%4)" : CUST "R(%7)"
NAME.KEY = TOP.START
IF NOT(ReverseSort) THEN
SETINDEX A.CODE, NAME.KEY ON ARTLEDGER ; ** UD version
END ELSE
TEST.KEY = TOP.START : ICONV('7B', 'MX0C') ; ** UD version
SETINDEX A.CODE, GE TEST.KEY ON ARTLEDGER ; ** UD version
READXFWD NAME.KEY FROM ARTLEDGER ELSE ; ** UD version
SETINDEX A.CODE, LAST_ALT_KEY ON ARTLEDGER ; ** UD version
END ; ** UD version
END
*
** Loop through transactions
LOOP
IF ReverseSort THEN ; ** UD version
READXBCK NAME.KEY FROM ARTLEDGER ELSE DONE = 1 ; ** UD version
END ELSE ; ** UD version
READXFWD NAME.KEY FROM ARTLEDGER ELSE DONE = 1 ; ** UD version
END ; ** UD version
KEY.ID = @ID ; ** UD version
IF NOT(DONE) THEN ; ** UD version
treeClient = FIELD(KEY.ID, '*', 1)
treeCust = FIELD(KEY.ID, '*', 2)
RefNo = FIELD(KEY.ID, '*', 3)
*
** Validate client and customer
IF treeClient NE CLIENT THEN EXIT
IF treeCust NE CUST THEN EXIT
*
** Read transactions
READ HREC FROM ARTLEDGER, KEY.ID ELSE HREC = NULL$
SOURCE = HREC<ARTLEDGER.SOURCE>
DESC = HREC<ARTLEDGER.DESC>
TDATE = HREC<ARTLEDGER.TDATE>
AMOUNT = HREC<ARTLEDGER.AMOUNT> + 0
ACCT = HREC<ARTLEDGER.ACCTS>
HNAME = HREC<ARTLEDGER.LNAME>
PREV = HREC<ARTLEDGER.PREV>
CURR = HREC<ARTLEDGER.CURR>
CUSTNO = TRIM(HREC<ARTLEDGER.ASSOC_CUST>)
*
** Build output line
TextLine = OCONV(TDATE, 'D2-'):SP2:RefNo "L(#9)":HNAME "L(#27)"
TextLine := DESC "L(#48)":AMOUNT "R26,C(#11)":SP2
TextLine := PREV "R26,E(#11)":SP2:CURR "R26,E(#11)"
PRINT TextLine
END ELSE EXIT
REPEAT
This should get you pointed in the right direction. HTH,
Bill
------------------------------------------------------------------------
----- Original Message -----
*From:* [email protected]
*To:* [email protected]
*Date:* 7/20/2011 12:04 PM
*Subject:* Re: [U2] SELECT question, paginated select.
Bill,
I guess my question is regarding how to "traverse" the index. In this case the
field we're using is the @ID, so it's
indexed by default. Below you stated that we have to traverse in BASIC, is this
using the SAVE.LIST approach that Doug
talks about?
In your example:
SELECT VENDCHECKS WITH INDEX_1 = "00CAPGE]"
how would you limit that to the first 10 entries? And then how would you get
the next 10 after that?
We're on UniVerse so I assume the UniData commands below won't work for our
application.
Chris
Date: Wed, 20 Jul 2011 11:39:42 -0700
From: [email protected]
To: [email protected]
Subject: Re: [U2] SELECT question, paginated select.
Chris:
For instance, we have checks file. A number of our clients have over a
million records in the file. It's indexed on vendor no. and check
date. The index would look like:
00CAPGE15857 (7 digit vendor and 5 digit date)
This is a left justified so it's easy to sort and select. I can select
any item in the file for any vendor and even limit it to any date...
SELECT VENDCHECKS WITH INDEX_1 = "00CAPGE]"
SELECT VENDCHECKS WITH INDEX_1> "00CAPGE15786"
Within a second or less you'll get your data since an index is being
selected. If you want to "traverse" the index, you'll need to do it
within BASIC. In D3 they had the KEY() function, In UniData they have
the SETINDEX, READFWD, and READBCK, commands (with a few other
derivatives). But these allow you to set an index to start with (let's
say in wanted to start accessing checks with vend# "CAPGE" and with
checkdate of 21 Mar 2011). From here you can read forward or backward
for {n} number of records. When you have the number you want you need
to return to the calling program with this data including the top and
bottom values. You'll display what you want then if you go to the next
page you can READFWD from the bottom of the page. To go backward you'll
READBCK from the top of the page.
For UniVerse, you'll have to do a bit of a dance, although, compared to
D3 you have to do a bit of a dance in UniData. But I know UniData works
well and assume it's also true with UniVerse, although UV doesn't store
in a B-Tree, so the first item read may have a large number of items in
the list.
HTH,
Bill
------------------------------------------------------------------------
----- Original Message -----
*From:* [email protected]
*To:* [email protected]
*Date:* 7/20/2011 11:20 AM
*Subject:* Re: [U2] SELECT question, paginated select.
Bill,
I was curious if you had a quick example? I'm just curious how you guys are
suggesting to do this
Chris
Date: Wed, 20 Jul 2011 11:13:04 -0700
From: [email protected]
To: [email protected]
Subject: Re: [U2] SELECT question, paginated select.
Chris:
I'm betting the solution also needs indexing, because you want to pick
up where the last list left off and you don't want to wait for a select
to return to save the list. So, SAMPLE isn't really a solution but
indexing is. We do this all the time, but we have to use indexing. In
fact, we parse through a file with millions of records to get a page
full of stuff and the pop-up list is instantaneous.
Secondly, I'm not sure a dropdown list is the appropriate choice for a
thousand items.
HTH,
Bill
------------------------------------------------------------------------
----- Original Message -----
*From:* [email protected]
*To:* [email protected]
*Date:* 7/20/2011 10:14 AM
*Subject:* [U2] SELECT question, paginated select.
I'm working on a screen in our webapp where we're using a dojo widjet to create
a drop down with client names. The problem
is that we have 1,000's of clients and when we do a SELECT on this many clients
it takes a while. I know we can index the field but
I was wondering if you can do smart selecting in UniVerse.
For example if I wanted to use this query:
SELECT CLIENT WITH @ID = "A]" SAMPLE 10 (returns 10 clients starting with the
letter A).
I want to paginate my drop down so the first query has the above data. The
problem I'm facing is that I don't know how to
get the NEXT 10 clients? Is there a way to do this?
For example if I wanted to return the next 10 CLIENTS, without including any of
the first 10.
Chris
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
