Select doesn't work properly for number

[veewee7764]

The following is a select line:

"SELECT * FROM students
            WHERE stuid = :stuid AND pin = :pin AND stupct >= '80'";

The values in stupct are a percentage but as far as the field knows is it just a number

stuid and pin is pulled from a table. Both of these work properly.

The stupct is a percentage number which is also pulled from the same table. But, for some reason, if the percentage number is 100, it fails to pull the data.

The field is a number, just like the other fields, but for some reason, 100 won't return anything. 99.99 will return, but not 100. The other two fields values seem to work ok.

It is only this one stupct that fails. It should return on any number equal to or greater than 80, I just don't know why it won't work if the value is 100 (or more).

How can I get it to return with 100?

Hope I am explaining this so it can be understood.

The field (or column) is a varchar(6) but I have tried different values for (6) to no avail.

Any assistance would be appreciated.

[mac_gyver]

5 minutes ago, veewee7764 said:

The field (or column) is a varchar(6)

this is a character/string data type. comparisons between strings are character by character, and the character '8' is greater then the character '1'.

numbers should be stored in an appropriate numerical (integer or decimal) data type.

[veewee7764]

Awesome. . . Now I just have to go learn how to make that field a decimal, not a character!

That is what I needed!

 

[gizmola]

1 hour ago, veewee7764 said:

Awesome. . . Now I just have to go learn how to make that field a decimal, not a character!

That is what I needed!

That's one of the great things about SQL is that you have declarative syntax.  So you just issue an "ALTER TABLE MODIFY COLUMN ..." statement and it will be fixed.  Depending on the implementation it will often convert existing data, although sometimes that won't work.

A good way to test this sort of statement out in advance is to make a copy of the original table, which is also really easy in SQL:  "CREATE TABLE t_example as SELECT * FROM example".

This will make an exact copy of the table structure and data, although indexes and constraints aren't copied, but for testing an ALTER statement it works great.  It's also a quick and dirty way of having a backup of the data in a table when running any sort of large update you want to do interactively.  

Then you can run your ALTER statement on the t_example table to test it out first, and see if there are any warnings or syntax problems.

If you are REALLY careful, you can get the same sort of protection using transactions, but if you don't recognize a mistake, once you commit there's no way to recover the data you might have changed or removed.