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