Re: varchar2 speak the speech i pray you!

sack wrote:

> 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

Christian,

I didn't mean to make the problem seem any more complicated than a simple question. If I did that I apologize.

Let me put it in another way. I have not assigned the "." any special function. All I have is two tables, that's all. I'm not bringing over any macros or complicated queries to the database.

All my current database has is two tables.

The first that looks like;

Section Number                    Some DataA            SomeDataB
1.2.3.4                                    blahblahfadsfa
blahblahxvdlkfj
2.5.7.8.14                                dfjald                    fdsfl
10.2.3.2.4                                fadsl                        fdlsafj

The second table looks like;

Filename                    Section Number
fileA.doc                        1.2
fileB.doc                        2.5
fileC.doc                        10

Basically what I am trying to do is have a number like "#.#.#.#" act like a number and not like text. If I was to do a simple query like

SELECT *
FROM TABLEONE
ORDER BY SECTNUM the results come out to be;
1.2.3.4
10.2.3.2.4
2.5.7.8.14

Instead of;
1.2.3.4
2.5.7.8.14
10.2.3.2.4

that is my first problem

my second problem is that I would like to map a relationship where something like;

1.2 = 1.2.3.4
2.3 = 2.3.4.5.6.7.8 etc

the need for the mapping is so that I can then have a query that will do a join on both the tables and will show the original section number in table 1 with the associated filename in table 2.

in the end i hope to do something like;
WHERE SectionNumber > 1.2.3.4 AND < 1.9.9.9.9.9 to report or associate all 1.whatever to document fileA.doc etc.

I hope that clarifies the situation. Any help or comments you can give would be most appreciative.

Thanks,

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