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