Results 1 to 3 of 3

Thread: Select

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Unanswered: Select

    Hello, I don't really know why I doesn't work:

    SELECT
    (TO_NUMBER(substring(000101048987,0,1))*0)
    +(TO_NUMBER(substring(000101048987,1,1))*47)
    +(TO_NUMBER(substring(000101048987,2,1))*43)
    +(TO_NUMBER(substring(000101048987,3,1))*41)
    +(TO_NUMBER(substring(000101048987,4,1))*37)
    +(TO_NUMBER(substring(000101048987,5,1))*29)
    +(TO_NUMBER(substring(000101048987,6,1))*23)
    +(TO_NUMBER(substring(000101048987,7,1))*19)
    +(TO_NUMBER(substring(000101048987,8,1))*17)
    +(TO_NUMBER(substring(000101048987,9,1))*13)
    +(TO_NUMBER(substring(000101048987,10,1))*7)
    +(TO_NUMBER(substring(000101048987,11,1))*3)
    FROM DUAL
    /

    But If I do (putting another different costant at the beggining It works):


    SELECT
    (TO_NUMBER(substring(100101048987,0,1))*0)
    +(TO_NUMBER(substring(100101048987,1,1))*47)
    +(TO_NUMBER(substring(100101048987,2,1))*43)
    +(TO_NUMBER(substring(100101048987,3,1))*41)
    +(TO_NUMBER(substring(100101048987,4,1))*37)
    +(TO_NUMBER(substring(100101048987,5,1))*29)
    +(TO_NUMBER(substring(100101048987,6,1))*23)
    +(TO_NUMBER(substring(100101048987,7,1))*19)
    +(TO_NUMBER(substring(100101048987,8,1))*17)
    +(TO_NUMBER(substring(100101048987,9,1))*13)
    +(TO_NUMBER(substring(100101048987,10,1))*7)
    +(TO_NUMBER(substring(100101048987,11,1))*3)
    FROM DUAL
    /

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    I don't know how either works considering that "substring" is not an oracle supplied function!
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First query:
    This is the first line where your result becomes NULL:
    +(TO_NUMBER(substring(000101048987,10,1))*7)
    Why? Because, no matter how many 0's you add at the beginning of the number, you won't change it. Therefore, your 000101048987 becomes 101048987 which is 9 characters long. And you want its substring which starts at 10th position ... you won't find it, and therefore it results with NULL.

    Your second query works thanks to a pure luck ... 100101048987 is 12 chars long. If you added another (for example)
    +(TO_NUMBER(substring(100101048987,13,1))*3),
    it wouldn't work either.

Posting Permissions

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