For some reason when I try to do greater than or less than, it says that 100 is less than 2. I think it's only reading the first digit when it determines the greater/less value. Not sure why or if its suppose to be like this? Trying to create a Short Ship report for my database course.

enter image description here

  • 2
    Is it possible that the data you think are numbers are stored as VARCHAR2? Please add a table definition or this might be closed – kevinsky May 14 '19 at 13:02

Your quantity columns are being stored as strings, rather than numbers, causing (for example) 10 to be "less" than 9 - a string comparison is obviously based on the characters in the string rather than whole numbers, hence the result you are seeing.

Oracle is not converting the values to numbers when you do a comparison ('<' and '>' in your case), but it is implicitly converting them to numbers when you perform mathematical operations on them, hence the - works as expected.

If you cast the VARCHAR columns to a NUMBER when doing the comparison your query will work correctly:

select *
from orders 
where TO_NUMBER(qty)<TO_NUMBER(qtyshipped);

Having said that, you're better off recreating your table with the correct datatypes.

Example DB Fiddle to demonstrate all of this is here.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.