Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: varchar2 speak the speech i pray you!

Re: varchar2 speak the speech i pray you!

From: sack <news_at_webpre.com>
Date: 2000/01/30
Message-ID: <870mmb$k26$0@216.39.144.218>#1/1

Jack Montealegre wrote:  

> Let me put it in another way. I have not assigned the "." any special
> function.

Yes you did. It's your delimiter character between different levels of subsections (If it's not I don't know what you are talking about)

> 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
>

So all the second table does is tell you which file contains that section. You could either put away with that table completely and just add another column (say FILENAME) to your first table to achieve the same. Or even cleaner, instead add a column FILE_ID to your first table, and make your 2nd table look like

Filename File_ID

fileA.doc       1
fileB.doc       2
fileC.doc       3

> Basically what I am trying to do is have a number like "#.#.#.#" act like a
> number and not like text.

And that's where your problem is. You can't tell your database this column contains a VARCHAR2, and then expect it to treat it like a number.

Think about what information your string "#.#.#.#" contains. Is it just one parameter? No, its 4 paramters put into one string delimited by '.'s . If you intend to use all that information in your database you will need to put these four paramteres in their individual columns. But so far it doesn't look like you will be using all that information.

> 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
>

Sorry the suggestion to change from . to : was bull. As long as the column is a VARCHAR it will sort all data with the same first character together.

> 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.
>

changing your table structure as layed out above will make that trivial. for the < AND > comparission to work
reliable, even for cases like > 9.1 AND < 10.9, again you will need a different delimiter character (like :)

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US