Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Talking Unanswered: Plz provide me Solution

    Dear Friends!

    I created one table with two columns. Their types are varchar2 and length are 15 characters. for example:

    create table ins
    (ifrom varchar2(15), ito varchar2(15));



    after that I inserted one row in those columns:

    insert into ins
    values('1000000001','1000000010');


    after inserting the row, i did query with the following conditions:

    1. select ifrom,ito from ins
    where '1000000001' between ifrom and ito;

    result:

    IFROM ITO
    1000000001 1000000010

    it returned one row true. it is correct.

    BUT when i used these conditions:

    2. select ifrom,ito from ins
    where '1000000001rrr' between ifrom and ito;
    (add alphabate)

    OR

    select ifrom,ito from ins
    where '100000001' between ifrom and ito;
    (remove one zero)

    despite of that, this query executes the result. I think, it should not execute the result, because of condition is not matching. if executes, what is the reason behind it? I want solution not to execute this type of condition in between range in sql. How is it possible? please provide me solution.

    thanks
    shabana

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simply, this is how Oracle orders character values.
    Code:
    SELECT mbr FROM
    (SELECT '1001' mbr FROM dual
     UNION 
     SELECT '1010' mbr FROM dual
     UNION
     SELECT '1001rrr' mbr FROM dual
     UNION
     SELECT '101' mbr FROM dual
    )
    ORDER BY mbr;
    
    
    MBR
    -------
    1001
    1001rrr
    101
    1010
    Are "ifrom" and "ito" numbers? If they are, store them into the NUMBER datatype column. Or, you could convert them into numbers using TO_NUMBER function (which, of course, won't work if there's really value like '101rrr' in there).

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    If your data in ito and ifrom must only contain 0s and 1s, then add a CHECK constraint to enforce this, so that you don't have junk data :

    Code:
    rbaraer@Ora10g> create table ins
    (
        ifrom varchar2(15) CONSTRAINT check_ins_ifrom CHECK (regexp_like(ifrom,'^[0-1]+$')),
        ito varchar2(15) CONSTRAINT check_ins_ito CHECK (regexp_like(ito,'^[0-1]+$'))
    );  2    3    4    5
    
    Table created.
    
    rbaraer@Ora10g> insert into ins(ifrom, ito) values ('1000000001','1000000010');
    
    1 row created.
    
    rbaraer@Ora10g> insert into ins(ifrom, ito) values ('1000000001r','1000000010');
    insert into ins(ifrom, ito) values ('1000000001r','1000000010')
    *
    ERROR at line 1:
    ORA-02290: check constraint (RBARAER.CHECK_INS_IFROM) violated
    
    
    rbaraer@Ora10g> insert into ins(ifrom, ito) values ('1000000001','1000200010');
    insert into ins(ifrom, ito) values ('1000000001','1000200010')
    *
    ERROR at line 1:
    ORA-02290: check constraint (RBARAER.CHECK_INS_ITO) violated
    
    
    rbaraer@Ora10g> insert into ins(ifrom, ito) values ('1000000001','1000r00010');
    insert into ins(ifrom, ito) values ('1000000001','1000r00010')
    *
    ERROR at line 1:
    ORA-02290: check constraint (RBARAER.CHECK_INS_ITO) violated
    
    
    rbaraer@Ora10g>
    Here is the documentation about REGEXP_LIKE if you want to tune the constraints.

    BTW these strings of 0s and 1s seem a little strange to me and may come from a flawed design. No offense there but it is rare to see such things in good relational schemas. Could you tell us what they represent exactly ?

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    insert into ins values ('1000000001', '1000000010');

    is, actually, the same as

    insert into ins values ('513', '514');

    but it is easier to learn only two digits (0 and 1) instead of, for example, 8/10/16 of them.

    Just kidding!

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Littlefoot
    insert into ins values ('1000000001', '1000000010');

    is, actually, the same as

    insert into ins values ('513', '514');

    but it is easier to learn only two digits (0 and 1) instead of, for example, 8/10/16 of them.

    Just kidding!
    you just blew my mind
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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