Re: varchar2 speak the speech i pray you!

A copy of this was sent to sack <news_at_webpre.com> (if that email address didn't require changing) On 30 Jan 2000 02:25:20 GMT, you 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?
>>

you need to conver the a.b.c.d.e.f into discrete components which can be sorted (or make them all the same length, eg: if when constructing the document ids you made them be 001.002.003.004.005.006 and 010.001.003.005 you would find the ascii sort might work out for you as well).... If you did that -- then displaying the information in the 1.2.3.4.5.6 format is pretty easy, you would just:

ops$tkyte_at_8i> select replace( replace( ltrim( document_id, '0' ), '.0', '.' ), '.0', '.' ) from t;

that would turn 001.002.003 into 1.2.3

to take the existing data and use it, you can consider creating a view like this:

ops$tkyte_at_8i> create table t ( x varchar2(25) );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into t values ( '1.2.3.4' );

1 row created.

ops$tkyte_at_8i> insert into t values ( '2.5.7.8.14' );

1 row created.

ops$tkyte_at_8i> insert into t values ( '10.2.3.2.4' );

1 row created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace view t_view   2 as
  3 select x,
  4 to_number(substr(x||'.',1,instr(x||'.','.')-1)) part1,   5 to_number(substr(x||'.',instr(x||'.','.',1,1)+1, instr(x||'.','.',1,2)-instr(x||'.','.',1,1)-1 )) part2,   6 to_number(substr(x||'.',instr(x||'.','.',1,2)+1, instr(x||'.','.',1,3)-instr(x||'.','.',1,2)-1 )) part3,   7 to_number(substr(x||'.',instr(x||'.','.',1,3)+1, instr(x||'.','.',1,4)-instr(x||'.','.',1,3)-1 )) part4,   8 to_number(substr(x||'.',instr(x||'.','.',1,4)+1, instr(x||'.','.',1,5)-instr(x||'.','.',1,4)-1 )) part5,   9 to_number(substr(x||'.',instr(x||'.','.',1,5)+1, instr(x||'.','.',1,6)-instr(x||'.','.',1,5)-1 )) part6  10 from t
 11 /

View created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from t_view
  2 order by part1, part2, part3, part4, part5, part6   3 /

X                              PART1      PART2      PART3      PART4      PART5

------------------------- ---------- ---------- ---------- ---------- ----------
----------
1.2.3.4                            1          2          3          4
2.5.7.8.14                         2          5          7          8         14
10.2.3.2.4                        10          2          3          2          4

that gives you upto 6 section numbers to order on.  

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

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation