Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Red face Unanswered: Wrong results with comparision operator

    Hi All,
    I'm trying to retrieve results where sys_version is <= 6.0.5, but the following query is giving other sys_versions with 10.0.1 also, please let me know what is wrong with the query.

    Also note that sys_version is varchar2 field which contains the system version number like 10.0.1

    Below is my query

    select hw_rev, SYS_VERSION from sys_details where SYS_VERSION <= '6.0.5' ORDER BY SYS_VERSION ASC


    Regards
    Sean

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are comparing alphanumeric strings, not numbers. '10.0.1' <= '6.0.5' is true because the very first character '1' <= '6'.

    You need to extract the components of the version number, convert them to numbers, and compare them as numbers, or reformat the strings to make comparisons work e.g. '0010.0000.0001' > '0006.0000.0005'.

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    Hi Tony,

    Thank you for the reply. Could you suggest which function is best to convert the numbers and do the comparision?

    Thanks
    Sean

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There are various ways - all quite painful!

    Here is one:

    Code:
    TRIM(TO_CHAR(TO_NUMBER(SUBSTR(sys_version,1,INSTR(sys_version,'.')-1)),'0000'))
          || '.'
          || TRIM(TO_CHAR(TO_NUMBER(SUBSTR(sys_version,INSTR(sys_version,'.')+1,INSTR(sys_version,'.',1,2)-INSTR(sys_version,'.'))),'0000'))
          || '.'
          || TRIM(TO_CHAR(TO_NUMBER(SUBSTR(sys_version,INSTR(sys_version,'.',1,2)+1)),'0000'))
    i.e.
    Code:
    SQL> select sys_version
      2  from
      3  (select '10.0.1' as sys_version from dual
      4   UNION
      5   select '6.0.5' as sys_version from dual
      6  ) the_table
      7  where TRIM(TO_CHAR(TO_NUMBER(SUBSTR(sys_version,1,INSTR(sys_version,'.')-1)),'0000'))
      8        || '.'
      9        || TRIM(TO_CHAR(TO_NUMBER(SUBSTR(sys_version,INSTR(sys_version,'.')+1,INSTR(sys_version,'.',1,2)-INSTR(sys_version,'.'))),'0000'))
     10        || '.'
     11        || TRIM(TO_CHAR(TO_NUMBER(SUBSTR(sys_version,INSTR(sys_version,'.',1,2)+1)),'0000'))
     12        <= '0006.0000.0005';
    
    SYS_VE
    ------
    6.0.5
    Nice, isn't it?!

  5. #5
    Join Date
    Sep 2008
    Posts
    5

    Sorry for the wrong info

    Hi Tony,

    Thank you for the query, its truly wonderful how we can modify data on the fly.
    I'd like to note one more issue with my data. Sone of the version numbers also contains characters within them like below:

    6.0.1
    5.5R6
    10.0.1
    10.0.4x29
    5.6.2R1
    6.0.3P2D4
    6.0.3
    6.0.5
    6.0.4

    so, Is there any way I can modify your query to suit the above data.

    Regards
    Sean

  6. #6
    Join Date
    Sep 2008
    Posts
    5
    I'm able to get the values, but I'm not able to use the alias in the where cluase, why is it so.

    Basically im taking the first numbers before the period and the first number after the initial period.

    select sys_version, trim(to_char(SUBSTR(sys_version,1,INSTR(sys_versio n,'.',1)-1), '0000'))
    || '.' ||
    trim(to_char(SUBSTR(sys_version,INSTR(sys_version, '.')+1,INSTR(sys_version,'.',1)-1), '0000')) T
    from sys_details where T <= '0006.0005'


    Thanks once again

    Sean

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Sean22
    I'm not able to use the alias in the where cluase, why is it so.
    If you mean you can't do this:
    Code:
    select [some expression] as x
    from y
    where x = 1; -- won't work
    then no, you can't. You either have to repeat the expression in the WHERE clause, or nest the select like this:
    Code:
    select x
    from (select [some expression] as x
          from y)
    where x = 1;

  8. #8
    Join Date
    Sep 2008
    Posts
    5
    It worked. Thank you very much for your help

    Sean

Posting Permissions

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