Using STIQ test can be frustrating.  Especially when it uses a bastardized selenium stored in an undocumented wiki format (the only thing you’re supposed to need to know is that a table is three pipe ‘|’ characters with one space before or after any content characters, or only one space if their are no characters in the cell, and that there are no spaces before the first or after the last pipe — that must have been fun to write a parser for; in this case a logical leniency would’ve been actually easier) that has a two pass eval that is preparsed before evaluating as javascript.

Whew!

Now try to parse a common number format like $1,234,567.89 into that.  It’s a minimum 4 step
operation to get to where you can string compare it against 1234567.89.  And very error prone.  Does it escape regexes?  Does it treat it as numbers or strings for greater/less than?  Does it need quoted to “cast” to a string?  Double or single quoted?  What if you have fractions or longer decimals, scientific notation, or other currency?  Don’t forget to guess when and how it evaluates variables, defines, and special collections like storedVars.

It can be done, but it isn’t fun.

A co-worker mentioned that when all you have is a hammer, you tend to use it for everything.  Even things it shouldn’t be used for.

After a couple hours puzzling over this problem, through trial and error, I finally got it working (for a reasonable expected input.)  Then I went to a stand up meeting, came back, and had an epiphany.  I was already using database functions like COUNT(), SUM(), and ROUND() — surely there are some number formatting functions as well.  A quick search found TO_NUMBER() which turned out to be the reverse of what I needed, which is TO_CHAR().

Oracle is a bit funny about data storage and display.  It probably has something to do with efficiency, and a lot to do with legacy compatibility.  Anyway, I’ve got workable query that looks something like this:

SELECT TO_CHAR(ROUND(SUM(time)/60), ‘9,999,999’) as time_in_minutes …

SELECT TO_CHAR(SUM(amount), ‘$9,999,999’) as amount …

Of course, I don’t know if I need to use ROUND, TRUNC, FLOOR, REMAINDER, or MOD.  I wonder how I can do date/time arithmatic in Oracle?  I think I’ve found my new golden hammer — until database portability becomes an issue.