Re: varchar2 speak the speech i pray you!

Jack Montealegre wrote:
>
> All,
>
> I'm in the process of importing a database from Access over to Oracle 8i
>
> for NT.
>
> The database consists of two tables a. the data b. the key
>
> a. is a collection of data that referencing information specific to
> documents, ie section numbers within the documents.
>
> b. is a mapping between the main section number and its association with
>
> a particular document.
>
> ex.
> 1.2.3.4 = Document A
> 2.3.4.5 = Document B
> 10.5.4.6 = Document C
>
> and so on. however also in the data there are hybrids of this mapping
> section number such as;
>
> 1.2.3.4.5.6 = Document A
> 2.3.7.4.2 = Document B
> 10.1.3.5 = Document C
>
> So as you can see i'm in a bit of a quandry. Initially in a Access DB
> the only way to have these types of numbers show up is to use the text
> data type.
>
> I tried using varchar2 as the datatype in Oracle however the same thing
> is happening when I do a order by section number. It reports;
> 1.2.3.4
> 10.2.3.4 (this should be after the last one)
> 2.3.4.5
>
> It seems it is organizing it dictionary style as it did in Access. Did I
>
> just waste my time? Or is there a magic cure in Oracle that will fix
> this?
>

Well, you expect Oracle to know something about your data without telling it that detail about your data.
You didn't tell us either, but luckily humans have the advantage of being capable of making sense even from incomplete information through relation to context and prior experiences.

Using that capability I was able to determine that you assigned the character '.' some special meaning. Oracle you will need to explicitely make aware of that special meaning, or otherwise design a data model for that meaning. Depending on your needs how you do that will vary. One way would be to translate your key into values for multiple columns, which might be named
VOLUME, BOOK, CHAPTER, SECTION, SUBSECTION1, SUBSECTION2 etc.

If all you need is a different result from "order by" you could change your delimiter character to a character which has a higher ASCII code than the numerals (e.g. : ; < = > ? @ or any of the letters)

Christian Received on Sun Jan 30 2000 - 00:00:00 CST