I have a new table where the key is from some set of codes specified by a client of mine. To be more specific it's the Dewey Decimal System. And one of the codes is "000"!!! (There are also codes like "002" and "070".) (And there are decimal ones, e.g, "636.08".) (Btw - I have the key as VARCHAR.)

How much trouble can I expect? Must I give up on that key altogether? Must I find some offensive replacement, like "000N"?

Right now I had a problem with a relational index. The "000" row is the master (mother) of rows "001", "002", etc. (So the relational index is from the table to itself. But that's always worked fine around here.) No way could I get the detail (daughter) keys of "000" into the relational index field. Well I found out at last how I could do it manually by first turning off the index and then edit the relational index field by hand. And just leave the index off. At least if I do it with Database Manager the relational index field data don't go away. I can live with this way of doing it since the table data will be static once the relational index data is in place.

But it doesn't bode well. What else can I expect?

Help!

Please.

- Oystein -

Oystein ..

Bode well is not the word for it. It will be a genuine PITA since the way numeric data is treated by OI / AREV is a little hard to bear. For example "000" "0" and "0.0" are all seen as being the same (even with VARCHAR data type. You can test this by writing a little piece of code like:

A="

A=000"

A=0"

A=0.0"

TXT='

IF A=A THEN

 TXT=EQUAL ':A:' ':A

ELSE

 TXT=NOT EQUAL'

END

AND SO FORTH. I THINK YOU'LL FIND THAT THEY ARE ALL THE SAME IN BASIC+. OK, NOW:

LOCATE ANY ONE OF THESE IN A RELATIONAL INDEX AND YOU WILL GET A HIT ON ANY VALUE THAT PRODUCES AN ARITHMETIC ZERO. DITTO WITH ANY OTHER NUMERICS "001" "1" "1.00", ETC.

THIS DROVE ME WILD IN A JEWELRY APPLICATION WHERE WE WERE USING A TEXT DESCRIPTION FIELD FOR SIZE, LENGTH, CARAT WEIGHT, ETC. THE ONLY THING THAT SAVED ME WAS THAT SIZE WAS IN MM (MILIMETERS) AND LENGTH WAS IN INCHES (") AND WEIGHT WAS IN GRAMS, ETC. I HAD TO FORCE MY USER TO ENTER THE UNITS NEXT TO THE NUMERIC WITHOUT SPACING. TO AVOID DATA ERRORS, I HAD TO WRITE A PARSER TO CHECK THIS WHEN THE DATA WAS ENTERED.

dammit, my caps lock key got stuck and I'm not going to type this over again, so pardon the shout, but then again maybe that's what's right anyway.

Don Miller

C3 Inc.

Oystein:

P.S. I have a UDC that gets around this for keys (ICONV/OCONV). Let em know if you want a copy.

Cheers..

Don

Oystein-

Just a quick idea- How about IoOptions? Will that do it?

Sorry- don't have the ability to test here…

Mike Ruane

Hi Oystein

I set up a new table and changed the key to text and I could save and retrieve records with keys of

0

00

0.0

000

and they all showed up as different records using system editor new record and open record. (and it helps if you remember to refresh - I forgot and lost key temporarily)

If you needed the things to sort nicely you could then write one of those calculated field things. Do a field on the decimal point.

I have a maths/computer science degree and I can't remember what a dewey decimal is.

Scott.

Janet,

Wasn't sure if you were being humorous or not. Just in case, I believe the Dewey Decimal system is a standard for library organization. The numbers represent literary categories.

[email protected]

SRP Computer Solutions, Inc.

Don/Don/Scott,

(Hey! - that sounds like two beats on a bass drum and one snare shot…

Don B is correct. The DDC (Dewey Decimal Classification) is a (hierarchical) set of codes used in libraries to classify the content of books and other publications. E.g, code 500 is Science, 510 is Mathematics, 519 is Probabilities & applied mathematics, 519.5 Statistics, etc. (And 513.14159265358979 is Theory of Pi. No, that was a joke.

See .

Scott - I tried with TEXT instead of VARCHAR, but it didn't help with the relational indexing problem.

But thanks a lot for prompting me on! Perhaps it will work after all. I suddenly realized I have a different table - for a different classification system - with keys both "00" and "000". And that table works fine!

I.e, checking again now I see I have the same problem with relational indexing there as well. In this other table "00" is the "mother" of "000". I only now discovered that the hierarchical relation between "00" and "000" doesn't work. One reason neither I nor my clients noticed before is because these codes are never used in practice. But in the Dewey system "000" has a more prominent place, with subordinate codes that are used a lot. So I need the relations to work.

But also in this other table I think I can fix the missing relation manually - like I described in my original posting.

Don M - perhaps I'm lucky and will avoid the problems you've encountered. My experience with that other table indicates I can.

But there's one future danger. So far both tables are static (imported from a text file in one go by me) and only used for lookup. But my "Dewey client" also wishes to be able to edit rows and enter new rows in a data entry form. Perhaps problems will creep back in. At least with the relational indexing, which then will need to be left on.

So if it's not inconvenient I'd much like to have your UDC.

- Oystein -

Øystein Reigem,

Humanities Information Technologies,

Allégt 27,

N-5007 Bergen,

Norway.

Tel: +47 55 58 32 42.

Fax: +47 55 58 94 70.

[email protected]

Home tel/fax: +47 56 14 06 11.

Your best bet is use the NUMTOCHAR_CONV user defined conversion.

The Sprezzatura Group

World Leaders in all things RevSoft

Oystein ..

This is a virgin copy of NUMTOCHAR_CONV UDC (before I diddled with it for my own purposes:

SUBROUTINE NumToChar_CONV(TYPE, InVal, Branch, ReturnVal)

/*

÷ VERSION : Advanced Revelation 3.1

÷ PURPOSE : Force numbers to strings

÷ AUTHOR :

÷ CREATED : March 24, 1993

÷ PROCEDURES : Called as a user defined conversion

÷ WARNINGS : The output conversion does not check the first character. If greater security is desired, check the first character before stripping it off.

÷ THEORY OF OPERATION :

On ICONV, stiff a non-numeric character on the front of the string.   On OCONV, strip the first character off.

This conversion should be used whenever strings that look like numbers need to be treated as strings.  For example, if you have codes like:

       1         1.0       01.0

They are treated as being equal if they're seen as numbers.  This conversion forces the system to treat them as strings, while allowing the user see them in their numeric form.

÷ REVISION HISTORY (Most CURRENT first) :

  DATE       IMPLEMENTOR     FUNCTION

1. ——- ———– ——–

MM-DD-YY     initials        Modification

*/

*÷ EQUATE Variables (Terminate with '$') :

EQU TRUE$ TO 1

EQU FALSE$ TO 0

EQU YES$ TO 1

EQU NO$ TO 0

EQU OTHERWISE$ TO 1

EQU NULL$ TO ""

EQU SPACE$ TO \20\

*÷ COMMON Variables (Terminate with '%') :

*÷ LABELED COMMON Variables (Terminate with '@') :

*÷ MESSAGES called (Terminate with '$') :

*÷ DECLARED - FUNCTIONS called :

*÷ DECLARED - SUBROUTINES called :

/*

÷ INDIRECT - FUNCTIONS/SUBROUTINES called if known (Make COMMENTS) :

*/

*÷÷ PROGRAM TOP

IF Type=ICONV" THEN

 ReturnVal=x" : InVal      ;* you can use any character you need

END ELSE

• ReturnVal=InVal1, LEN(InVal)

 ReturnVal=InVal2, LEN(InVal)

END

RETURN

Hope it's useful to you.

Don,

Thanks. So you store all values prefixed with an "x" (or some other non-numeric character). Seems to ring a bell. I think I've read about it on the list a long time ago, but never thought I needed it myself.

- Oystein -

Oystein ..

Yup. The problem with numerics that evaluate to be the "same" number value manifests itself in the following ways:

1. Sometimes a right-justified B-Tree index will treat them as the same so that searches fail.

2. A locate using 'AR' on the data can fail if a numerically equivalent value is found. Dest string 1.00@VM:1:@VM:01.0. LOCATE "01" in Dest String by 'AR' setting pos else .. tends to find the 01 in the destination string so the new value won't get inserted because the else branch won't be executed. This is essentially a problem in Relational Indexing as well.

3. Using the same Dest string in a loop with a test value for a match will find them all equal.

The UDC will treat them as strings and since they all use the same prefix the matching logic will work well since both the value and lengths will be used. Be sure to use the UDC ahead of all match operations. This should make your life easier.

Don Miller

C3 Inc.

Don,

Hmmm. Let me try to provoke you…

1. Sometimes a right-justified B-Tree index will treat them as the same so that searches fail.

But right-justificiation is normally used for numbers. And with numbers one would have a data type of e.g INTEGER or DECIMAL. And with those data types I would expect some normalisation to take place. Like both "000" and "00" being treated as "0". Fair enough.

Remaining problem: The few (?) cases where you need right-justification on alphanumeric data.

2. A locate using 'AR' on the data can fail if a numerically equivalent value is found. Dest string 1.00@VM:1:@VM:01.0. LOCATE "01" in Dest String by 'AR' setting pos else .. tends to find the 01 in the destination string so the new value won't get inserted because the else branch won't be executed. This is essentially a problem in Relational Indexing as well.

I don't get it. I thought Dest must be ordered by 'AR' to use LOCATE BY 'AR'. I can't see any order in your Dest data.

Oh, yes, I can. It's 'DL' ordered. ???

And when are 1.00 and 1 and 01.0 and 01 different? When they are not really numbers, that's when. When they're more code-like things.

You're cheating here. You're trying to keep values that are not really numbers partly ordered as if they were numbers, partly not. What kind of order is it really you want to achieve? A primary sort on value as if they were numbers (1.00=1=01.0=01)? Then some secondary left-justified sort?

3. Using the same Dest string in a loop with a test value for a match will find them all equal.

Same thing.

Btw - I think the problem that I've seen - with the relational index - is different. I think there somewhere in the relational indexing programming must be an if Key then statement instead of if Key "" then. The first version treats "000", "00" and "0" like if the key was absent. (Well, I should of course test "0" in addition to the others before presenting my hypotheses, but I'm fed up.) The second would recognize them as genuine key values.

The UDC will treat them as strings and since they all use the same prefix the matching logic will work well since both the value and lengths will be used. Be sure to use the UDC ahead of all match operations. This should make your life easier.

I can see the strengths of the UDC. It takes care of all access through edit controls. Other (read) access must perhaps go through a symbolic that removes the "x".

- Oystein -

Oystein ..

Of course you're partly right. In typing the example, I just chose values that would pass the NUM(nn) test and strung them together. If I had used the LOCATE BY syntax with 'AR' I have seen no difference in sorting at all when the data is defined as a number. In fact, I've seen a case where only the first value which evaluates to 1 get stored.

Note that the UDC I sent you is both an ICONV and OCONV function. When I test the data in code, I make sure that the incoming data is ICONV'd and leave the data in the relational field as is. In this way a normal string comparison is used. You can still do a right-justified comparison internally. If you need to get hierarchical numeric sorting not left-to-right, then it might make more sense to append the "x" to the end. Anyway, the ICONV/OCONV will work in forms and in output if it is the ONLY OCONV specification in the dictionary. If this is to be used as a key in a secondary table then make sure that however you define the source field is applied to the key of the secondary file.

By the way, it is very difficult to implement this kind of logic in a CROSS_REFERENCE index on textual data since it parses on delimiters only. Supposedly, cross references are handy for indexed parsing of textual data since you can specify a stop-list or a go-list (the latter is particularly useful when there are only a few words you want to maintain indexes on).

I've had to do a lot of this in an automotive parts masterfile where there are a bunch of different parts but which can freely interchange with one another such that they can resolve to a common part number. That's a different story altogether but does use relational indexing to group interchangable parts together. These keys are all generally alphanumeric though, but many of them have leading 0's in their key field. It has been a bitch to make sure that they don't get stripped out when there is no other alpha character in the key.

Don Miller

C3 Inc.

Hi Donald

Well that explains why it was vaguely familiar but I had no real idea what it was.

Scott

Don,

Thanks.

I have been lucky until now and haven't had much use for validation/conversion. Must confess it didn't even enter my mind to do it in the dictionary, only in the edit controls. Time to sit down and do some experiments and get to know the matter…

- Oystein -

View this thread on the forum...