Negative, Captain
Seen in the wild:
... WHERE substr(amount,0, 1) != '-'
If you wanted to query a table of monetary transactions for any refunds (i.e. where the transaction amount is negative), how would you do it? Perhaps you’d think about avoiding problems that might occur if the default number format were to change, hm?
(before you say it: no, there is no index on amount, so it wasn’t a misguided attempt to avoid an index access path…)
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Gary
22 July 2010 - 9:33 am
Sad to say, but my instinct is to ask whether they are storing the amount as a character column.
I do keep trying to tell developers that, in the real world, we start counting from 1, not 0. Sometimes it helps to actually show them on their fingers.
Jeffrey Kemp
22 July 2010 - 9:44 am
Nope, definitely is a NUMBER 🙂
I see you spotted the benign zero there, too.
What surprised me a bit is that they were sophisticated enough to use the
!=operator instead of<>, which is more common with newbies.