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

closed as off-topic by Colin 't Hart, mustaccio, kevinsky, John Eisbrener, John aka hot2use May 15 at 7:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, mustaccio, kevinsky, John Eisbrener, John aka hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

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

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