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'.
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:
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'