Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    This isn't an Access question, however it is a question regarding a database, so I hope somebody can help me out!

    I've got an Oracle database (9i), that has a varchar2 column (ID) on a table (entity).

    The data I'm filtering all have values in this column beginning with TRS- (for example, TRS-1, TRS-29, TRS-1883).

    I've been able to create sql statement to sort the values numerically, by ignoring the TRS- characters:

    Code:
    select id from entity where id like 'TRS-%' order by to_number(substr(trim(id),5));
    The outcome would sort the above values as:

    TRS-1
    TRS-29
    TRS-1883

    rather than the alpha order of 1, 1883, 29.

    This works fine and dandy, until there's an input error on the ID column.

    What code can I use to create another view for "trouble shooting"? I'm looking to create a view that will basically show only the problem values (for example, TRS-a312, TRS- 221, TRS-?213).

    There is no input validation on the ID column. It's simply user entry. Staff have been in the habit of creating a smart number system (thus the TRS-) for entities of like attributes. In this case, they are all traffic road signs. Also of note, the ID column is 20 characters in length. These entities are in order, from 1, to a current value of 5730.

    Thanks in advance for any help you can offer.
    Christopher Baldrey

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans - I will check this out in the morning at the office.
    Christopher Baldrey

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •