Unexpected output in Oracle SQL

Question

On executing the query

select * from employee
where salary <= previous_salary

I'm getting a wrong output as follows (it's returning the entire table). I'm doing this in Oracle Application Express Edition

ID  NAME    SALARY  DEPT    PREVIOUS_SALARY
1   nadeem  1000    10      1500
2   kutu    1000    10      1500
3   aisha   1500    20      500
4   aisha   1500    20      500

and on executing this query

select * from employee
where salary >= previous_salary

I get no data found.

Please note that the guesswork would be removed if you included the schema of your table (type information) in the question. — Jonathan Leffler
– Jonathan Leffler, Commented Jan 4, 2012 at 7:54
thanks a million for he answer sir i was sitting at this same error for nearly 2 days thanks a lot — user1129400
– user1129400, Commented Jan 4, 2012 at 8:18
Welcome to Stack Overflow. Please note that the way to say 'thanks' around here is via up-votes for helpful answers (once you have enough reputation to give them), and by bestowing an 'accept' on the most helpful answer (which you can always do for questions you've asked, and which also gives you a small boost to your own reputation). See also the FAQ. — Jonathan Leffler
– Jonathan Leffler, Commented Jan 4, 2012 at 8:29
@user1129400: please remember to post your table definitions the next time you ask a question like that. — user330315
– user330315, Commented Jan 4, 2012 at 13:46

Jonathan Leffler · Accepted Answer · 2012-01-04 07:51:45Z

13

You've probably used a CHAR or VARCHAR2 type for the SALARY and PREVIOUS_SALARY columns.

When compared as strings, the data should work as you claim it does.

If you change the columns containing numbers to a numeric type, then the numeric comparisons will work as you intended.

answered Jan 4, 2012 at 7:51

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user359040 Over a year ago

+1 Or compared to_number(salary) >= to_number(previous_salary) - but if it's possible to change the datatypes to numeric, these should definitely be numeric!

user330315 Over a year ago

Good catch. And it shows how important it is to include the full table definition in the question.

Collectives™ on Stack Overflow

Unexpected output in Oracle SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related