Thursday, January 29, 2009

Where did Dunkelheit und Strafe go?

I've been told I shouldn't use obscenities in this blog, words like "pelosi", phrases like "sassen frassen fricken fracken" and certainly nothing from the Seven Words You Can Never Say on Television.

To that end, the Blog List has been culled again...

[fretful sighing sounds]
...the link to Dunkelheit und Strafe has been moved to The Other Place.

Why? Well, the website name, that's why. No, it's not "Dunkelheit und Strafe", that's my lame attempt to translate "Gloom and Doom" into German.

And it's not really called "Gloom and Doom" either, it's called... um... FcekudStartup.com.
Isn't that cute? Rearrange all the letters of a word except the first and last, and you can STILL read it!
The other problem with Dunkelheit und Strafe it that it always sorts to the top because in these grim economic times there's always some company going under or dumping their most valuable assets.

So, while I was at it (culling the Blog List), I pushed the "off topic" entries over to The Other Place as well: Jessica Hagy's Indexed and Randall Munroe's xkcd.

Here's my thinking: I want this blog to be accessible to people with no sense of humor at all... you know, like Management.

Samson in Carnivale

It's all about diversity!

Wednesday, January 28, 2009

SELECT FROM Excel Spreadsheets

Q: How do I run SQL SELECT queries against data stored in Excel spreadsheets?

(This is the opposite direction from importing data into a spreadsheet from a database; e.g., via Excel - Data - From Other Sources - From Microsoft Query, etc.)
A: You can use SQL Anywhere's proxy table feature to treat an Excel spreadsheet like a relational table, thus bringing the power of SQL SELECT to bear on spreadsheet data.

First, a simple example, then some tips and suggestions. Here is an Excel spreadsheet containing website hit counts by browser (MSIE 7, Firefox 3.03, etc):



Here's the code to answer the question, "Who's winning the browser wars, IE or Firefox?". Hit counts are summarized by browser brand name, getting rid of the version number differences:
CREATE SERVER EXCEL 
CLASS 'ODBC' USING 'Excel 2007';

CREATE EXISTING TABLE proxy_browsers
AT 'EXCEL;C:/temp/browsers.xlsx;;Sheet1$';

SELECT proxy_browsers.browser AS brand_name,
SUM ( proxy_browsers.hits ) AS hit_count,
hit_count / total_hits * 100.0 AS percent
FROM proxy_browsers
CROSS JOIN ( SELECT SUM ( hits ) AS total_hits
FROM proxy_browsers )
AS summary
WHERE browser IS NOT NULL
GROUP BY proxy_browsers.browser,
summary.total_hits
ORDER BY hit_count DESC;
Here is what the output looks like; Firefox is winning for this particular website (this blog, actually):



You can read about setting up proxy tables for Excel here:Here is what the table definition looks like in the SQL Anywhere catalog:
CREATE EXISTING TABLE DBA.proxy_browsers -- proxy
-- (
-- hits DOUBLE NULL,
-- pct DOUBLE NULL,
-- browser NVARCHAR ( 255 ) NULL,
-- version NVARCHAR ( 255 ) NULL
-- )
AT 'EXCEL;C:/temp/browsers.xlsx;;Sheet1$';
Here's what the data looks like:
SELECT * 
FROM proxy_browsers
ORDER BY hits DESC;


Here are some tips and suggestions:
  • The official name of the "proxy table" feature is "Remote Data Access"... it helps to know that when you're searching the docs.

  • The syntax for the CREATE EXISTING TABLE ... AT clause goes like this:
    '<CREATE SERVER name>;<filespec>;;<sheet name>$'

  • You may or may not need to code the "magic dollar sign" after the sheet name in the AT clause. If you do need it, and you don't code it, you will see this bogus message bubbling up from the Excel driver:
    The remote table 'EXCEL.C:/temp/browsers.xlsx..Sheet1' could not be found
    SQLCODE=-666, ODBC 3 State="HY000"
    Aside: Don't you try to code the AT clause using periods '.' like in that error message, you need to use semicolons ';' to avoid ambiguity with the filespec.

  • You only have to set up one ODBC DSN for everything, not one DSN for each sheet or workbook. You do have to use "Select Workbook" to point the DSN at a specific file, but after that the file and sheet names are specified in your SQL code. The file named in the "Select Workbook" doesn't even have to remain in existence.

  • Here's how the ODBC Administrator was used to create the "Excel 2007" DSN for this article:






  • The ODBC DSN must be set up on the same computer that's running the SQL Anywhere engine. If it's a network server on some other computer, that's where the DSN is located, and (unless you're using a mapped drive or UNC file specifications) that's where the Excel file is located as well.

    Plus... if the server is started as a service, the DSN must be a "System DSN", not a "User DSN".

  • In related news, the ISQL INPUT and OUTPUT statements no longer support the Excel format in SQL Anywhere 11. Plus, ISQL INPUT cannot read Excel 2007 *.xlsx files in SQL Anywhere 10: "The file may be in an unsupported format, or it could be corrupt."

    These changes are no big deal, since proxy tables work so well, except for the fact that any behavior change is a pain in someone's patootie, and the INPUT and OUTPUT commands specify files that are relative to the client whereas (see above) proxy table DSNs are relative to the server.

    And before anyone says "LOAD and UNLOAD now have CLIENT FILE clauses!", check out how well LOAD and UNLOAD work with proxy tables:
    Feature 'unload from non-SQL Anywhere tables' not implemented
    SQLCODE=-134, ODBC 3 State="HY000"
    Workarounds are welcome, this blog invites comments!

  • You might get away with having the workbook open in Excel at the same time you're running proxy table queries... or not.

  • The names in row 1 of the spreadsheet are used to generate (and match) column names in the proxy table. If you see an error like "Too few parameters. Expected 8." it may be because you have included spaces in the row 1 column names.

    If you really want spaces, you'll have to code an explicit column name list in the CREATE EXISTING TABLE, with "quoted column names" and data types... DOUBLE and NVARCHAR ( 255 ) seem to be good choices.

  • The Microsoft ODBC drivers for Excel 2003 and 2007 seem to impose limits on string lengths: 64 bytes if you're not lucky, 255 bytes if you are. "NVARCHAR" is a good luck talisman here.

  • Only the final cell data values are received via the proxy table, not formulas.

  • Excel data is not intended for simultaneous access by multiple connections, so don't go trying serve up your spreadsheets in an OLTP system.

  • Tip: A quick and dirty way to copy and paste data from the web or some other source into Excel without carrying over fancy formatting is to copy and paste into Notepad first, then copy and paste from Notepad to Excel; e.g., by Ctrl-A and Ctrl-C from Notepad and then Ctrl-V into Excel. This works well if the data pasted into Notepad turns out to be tab-delimited, which it often is when coming from a website.

  • Tip: Here's how to turn a SQL Anywhere result set into a tab-delimited text file, suitable copy and paste from Notepad into Excel:
    UNLOAD 
    SELECT *
    FROM ttt
    ORDER BY ccc
    TO 'c:/temp/ttt.txt'
    DELIMITED BY '\X09' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;

  • Proxy tables work with more than Excel; you can join Oracle tables with SQL Server, for example. SQL Anywhere's own Migrate Database Wizard uses proxy tables to set up the schema and suck in the data when you're converting from some other DBMS.

  • High volume proxy table queries sometimes run slowly. For example, complex cross-server queries don't get optimized the same way local queries do. Cross-server optimization may improve in the future but it's likely that local optimization will always be better. That's another reason not to serve up proxy tables in an OLTP environment.

  • Having said that, folks who like proxy tables like them a LOT. It's been one of the most popular features in SQL Anywhere since 1998.

Monday, January 26, 2009

A New Look, Sort Of

This blog has a new look, sort of...

  • A "Search This Blog" button has been added. It uses the wonderful Google Custom Search Engine (CSE) with the following "Included Site" URL patterns:
    sqlanywhere.blogspot.com/*/0*/*
    sqlanywhere.blogspot.com/*/1*/*
    Those patterns cut down duplication by excluding multi-article archive pages. For example, .../2008/06/automating-drop-table.html is included but not .../2008_06_01_archive.html.

    Justification: The builtin "SEARCH BLOG" button on the toolbar is almost useless because it shows the full text of all matching articles, and if you use Google itself to search sqlanywhere.blogspot.com you'll see those duplicate archive pages.

    Future: More buttons might be added, like "Search all the sites in the Blog List", "Search all the SQL Anywhere forums", "Search Sybase" and so on.

    I wish EVERY site used Google CSE. Instead, many companies try to build their own search engines and end up instead with a big load of pelosi.

  • The Blog List is now sorted by "Most recently updated". The display is limited to 10 blogs by default, with a "see all" button that will appear when I get around to adding more than 10 blogs to the list.

    Future: If the list grows very long and very active, 25 blogs might show by default... but I doubt it. It might even get cut down to 5.

  • The blog list has been un-culled; i.e., I have restored Biff's Rows and Columns, and not just because he came in from the barn.

    Future: Blogs will be culled for (my) lack of interest. Right now, Jessica Hagy's Indexed is in danger because it hasn't made me laugh in quite a while.

    On the other hand, Randall Munroe's xkcd reminds me of Gary Larson's The Far Side... sometimes lame (or unfathomable?), but more often than not it's LOL good.

    The daily grind kills cartoonists, I wish them both well.

  • The keyword label list has been replaced by a much shorter list of "Categories", and each article has been tagged with one single category:

    • announcement

    • database migration

    • housekeeping

    • off topic

    • performance and tuning

    • server setup and admin

    • SQL code

    • testimonial

    "Housekeeping" articles (like this one) talk about this blog, "off topic" articles include attempts at humor, and "testimonials" are heavy on the hyperbole and light on the details. The rest should be self-explanatory.

    Justification: The manually-maintained keyword list was both hard to maintain and not very good. "Search This Blog" is way better.

    Future: The "Categories" list might be dropped altogether. It's be more useful than the old keyword label list, but that's not saying much.

    On the other hand, if "Search This Blog" could be limited by category... hmmm.

    In the meantime, the categories list is complete and consistent and easy to maintain, and it doesn't take up much room on the page.

  • Oh, I almost forgot... the "Minima Stretch" template now gives YOU control over the display width.

Friday, January 23, 2009

Everything Looks Like a Database

"When your only tool is a hammer, everything looks like a nail."

That's what a client said to me years ago, when he saw me writing a SQL script to perform a task for which some other tool was better suited. Java, Perl, C#, I don't remember what he had in mind, but it was something outside the database, something on the client side.

It's one of those comments that sticks in my mind, surfacing from time to time to bother and provoke me. On the one hand, he was right, on the other hand... he hired me for database and SQL skills, not client-side programming.

And for many years, since the introduction of SQL Anywhere Version 6 (the one with Java classes as column data types), I have specialized in SQL Anywhere and MobiLink.

I've always been a specialist, one specialty at a time. Once upon a time it was PL/I, and I did everything in PL/I including tasks for which it was "inappropriate" just so I could be come more and more proficient. Then it was Turbo Pascal. Followed by PowerBuilder.

Now it's SQL.

Other folks are generalists. Some of them are really good at it, others not so much:

"Jack of all trades, master of none."
And that would be me, if I tried to be a true generalist.

But enough about history, let's code some SQL! Let's follow step-by-step through writing a SQL script to do something completely inappropriate, like initializing an empty "personal schedule" text file:



What follows actually happened. It might not be the way you like to do things, but it is what it is:
-- Step 1: Start coding! WISCA Rules!

SELECT STRING (
something )
FROM something
WHERE schedule_date BETWEEN '2009-02-01'
AND '2009-12-31';

-- Step 2: Code the STRING output, or at least some of it.

SELECT STRING (
IF something
THEN '==========\x0d\x0a'
ELSE ''
ENDIF,
DATEFORMAT ( schedule_date, 'Ddd Mmm Dd' ),
'\x0d\x0a' )
FROM something
WHERE schedule_date BETWEEN '2009-02-01'
AND '2009-12-31';
I can never remember exactly how to code DATEFORMAT's slightly funky format string so I always have to look it up, here (turns out this time I got it right, from memory, but still looked it up to be sure).

The '\x0d\x0a' is good ol' carriage return / line feed... been around since teletype days... Notepad and Wordpad on Windoze like CR/LF, the Mac and Nix/Nux got it wrong :)
-- Step 3: Start coding the FROM something.

BEGIN
DECLARE @from_date DATE;
DECLARE @to_date DATE;
SET from_date = '2009-02-01';
SET to_date = '2009-12-31';

SELECT STRING (
IF something
THEN '==========\x0d\x0a'
ELSE ''
ENDIF,
DATEFORMAT ( schedule_date, 'Ddd Mmm Dd' ),
'\x0d\x0a' )
FROM ( SELECT DATEADD ( DAY, something, @from_date )
AS schedule_date
FROM something
WHERE something ) AS date_gen
WHERE schedule_date BETWEEN @from_date AND @to_date;
END;
The BEGIN block has been coded so the DECLARE statements can be added, and the DECLARE statements have been added to "parameterize" the code: two references to @from_date but still only one literal '2009-02-01'.

The inner ( SELECT ... ) is called a "derived table", lets you code a view without messing around with CREATE VIEW. Here it's being used to generate (somehow) the date values used in the outer select.

The WITH clause is another way to code an in-line view, ahead of the outer SELECT clause instead of inside the FROM. However, I can remember how to code a derived table, I still have to look up WITH, so derived table it is.
-- Step 4: Add enough code to actually run a TEST.

BEGIN
DECLARE @from_date DATE;
DECLARE @to_date DATE;
SET @from_date = '2009-02-01';
SET @to_date = '2009-12-31';

WITH date_gen AS (
SELECT DATEADD (
DAY, row_num, @from_date ) AS schedule_date
FROM sa_rowgenerator (
0,
DATEDIFF ( DAY, @from_date, @to_date ) ) )
SELECT STRING (
IF 1 = 0
THEN '==========\x0d\x0a'
ELSE ''
ENDIF,
DATEFORMAT ( schedule_date, 'Ddd Mmm Dd' ),
'\x0d\x0a' )
FROM date_gen;
END;
OK, so a WITH clause HAS been added, creating an inline view called date_gen.

The sa_rowgenerator system procedure "returns a result set with rows between a specified start and end value", an enhancement to the old RowGenerator table which only had 255 rows. SQL Anywhere lets you treat a procedure call just like a table; i.e., inside the FROM clause.

The DATEDIFF function is used to specify the upper bound on the resulting column called sa_generator.row_num.

All of a sudden, the WHERE clause is no longer needed, since the call to sa_generator creates only the rows that are needed.

Time to actually test it, in dbisql... so far, so good:

-- Step 5: Tackle the IF THEN ELSE.

BEGIN
DECLARE @from_date DATE;
DECLARE @to_date DATE;
SET @from_date = '2009-02-01';
SET @to_date = '2009-12-31';

WITH date_gen AS (
SELECT DATEADD (
DAY, row_num, @from_date ) AS schedule_date
FROM sa_rowgenerator (
0,
DATEDIFF ( DAY, @from_date, @to_date ) ) )
SELECT STRING (
IF MONTH ( FIRST_VALUE ( schedule_date )
OVER date_window )
<> MONTH ( LAST_VALUE ( schedule_date )
OVER date_window )
THEN '==========\x0d\x0a'
ELSE ''
ENDIF,
DATEFORMAT ( LAST_VALUE ( schedule_date )
OVER date_window,
'Ddd Mmm Dd' ),
'\x0d\x0a' )
FROM date_gen
WINDOW date_window AS (
ORDER BY schedule_date
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW );
END;
My favorite OLAP aggregate functions are FIRST_VALUE and LAST_VALUE. Here they are used in the "IF MONTH ( ..." expression to detect every change the month between successive dates and insert the '==========\x0d\x0a' separator lines.

The WINDOW clause is a necessary evil; too bad you can't just code "give me the previous row". Instead, you have define a "sliding OLAP window" to include the previous and current rows. The WINDOW clause is extremely flexible and powerful; in this case, it's like driving your car to take the garbage to the curb (hey, I do that too!)

Still looking good:



Don't worry about the missing line break after the ========== separator. It's there, dbisql just doesn't show it.
-- Step 6: Finish up by writing to a file.

BEGIN
DECLARE @from_date DATE;
DECLARE @to_date DATE;
SET @from_date = '2009-02-01';
SET @to_date = '2009-12-31';

UNLOAD
WITH date_gen AS (
SELECT DATEADD (
DAY, row_num, @from_date ) AS schedule_date
FROM sa_rowgenerator (
0,
DATEDIFF ( DAY, @from_date, @to_date ) ) )
SELECT STRING (
IF MONTH ( FIRST_VALUE ( schedule_date )
OVER date_window )
<> MONTH ( LAST_VALUE ( schedule_date )
OVER date_window )
THEN '==========\x0d\x0a'
ELSE ''
ENDIF,
DATEFORMAT ( LAST_VALUE ( schedule_date )
OVER date_window,
'Ddd Mmm Dd' ),
'\x0d\x0a' )
FROM date_gen
WINDOW date_window AS (
ORDER BY schedule_date
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW )
TO 'c:/temp/temp_schedule.txt'
DELIMITED BY ''
ESCAPES OFF
HEXADECIMAL OFF
QUOTES OFF;
END;
Wrapping the SELECT with UNLOAD ... TO turns the output into a file. The four options (DELIMITED BY, etc) are the standard overrides to stop UNLOAD from doing any fancy formatting.

All done! Ready to use (once I had a PDA, now I use Wordpad :)...

Thursday, January 22, 2009

Support Is Ending!

The End Is Nigh!

Hurry! Hurry! Hurry!

It's the End Of Life for SQL Anywhere 9!

Seriously, it is the end of Engineering Support, which just means no more new point releases, no more EBFs... and it's not happening for a whole year (January 31, 2010).

You will still be able to get Tech Support after that date. Folks are still getting Tech Support for version 5.5.

On the other hand... a lot of shops don't like the thought of using software that can't be fixed or enhanced or upgraded to a new operating system. For you folks I suggest going straight to Version 11... and (ahem) if you need any help migrating, just ask.

Tuesday, January 20, 2009

Your Morning Smile

I usually expect to get my laughs from xkcd, not Joel on Software...

"One more comment... about purchase orders... we, personally, like money, here at Fog Creek, and will accept payment in just about any reasonable form customers would like to pay. Credit card, debit card, check, purchase order, cheques drawn on a foreign bank, small coins taped to an index card and mailed to us, Kruggerands, big stones from Yap, as long as it's not friggin' CATTLE we'll accept it in payment. It's money. Money is why you do this. In the entire history of Fog Creek the number of companies who paid with a purchase order and then stiffed us is, I think, zero, although there might be one I don't know about. It is literally not even worth the cost of doing a credit check."

- Joel Spolsky post in discussion on purchase orders
"Everybody needs money. That's why they call it money."
- Danny DeVito as Bergman in Heist, written by David Mamet

Monday, January 19, 2009

Readable, Correct, Concise

Question: How do I format a number with commas and a dollar sign? I want to format -123456789 as '-$1,234,567.89'.

Answer: The glib, dogmatic answer is "You don't do stuff like that in SQL, you do it on the client side!"

Sure, but what if the database server IS part of the "client side"? The boundaries are getting blurred these days. Consider web services, where a SQL Anywhere stored procedure can return fully-formatted XML, JSON or HTML text to the client side.

In that case, some of the traditional client-side tasks (like formatting a number with commas) may be performed on the server side (by SQL code inside a stored procedure).

Here are the specs:

  • Receive a DECIMAL ( 30, 2 ) input argument like 0 or -9999999999999999999999999999.99 and return it as a left-justified VARCHAR ( 50 ) string like '$0.00' or '-$9,999,999,999,999,999,999,999,999,999.99'

  • Omit the sign for positive values.

  • Always show the '$' sign.

  • Always show at least one digit to the left of the decimal point.

  • Always show exactly two digits to the right of the decimal point.

  • If the input is NULL, return NULL.

  • If an EXCEPTION is raised inside the function, RETURN '???' after logging the error via MESSAGE ... TO CONSOLE DEBUG ONLY.
Here are some test cases displayed in dbisql:



Here are some comments and caveats:
  • The input is fixed-point DECIMAL, not floating point, simply to encourage the calling code to answer the question "Do I round or truncate?" before calling the function. The implicit default is "round to two decimal places", and that happens at the interface when the actual input value is converted to DECIMAL ( 30, 2 ).

  • Here's a general rule of thumb: FLOAT and DOUBLE data types should never, ever be used for exact monetary values. I feel strongly about that, especially about the "mysterious rounding errors" mentioned here:
    "Programmers writing financial applications often mistakenly use floating-point datatypes to represent monetary values, such as dollars and cents. Doing so results in mysterious rounding errors where the total of a column is slightly different from the exact arithmetic sum of all integer values that represent the currency amounts." - Sybase Technote Using the float Datatype

  • If conversion errors are going to happen with this function, they're going to happen at the interface; e.g., when an attempt is made to pass 'xxx' as input.

  • The function is coded to always return something; e.g., NULL or '???'.

  • There are no funky RAISERROR, SIGNAL or RESIGNAL statements to confuse the programmer. If the function's going to be used in a life-critical environment, it's up to the calling code to check for '???' return values.

  • Of course, a function like this isn't likely to be used in a life-critical environment. However, it might be used in consumer-oriented website, and nothing is more likely to drive a customer away than seeing a cryptic internal error message bubbling up to the browser. That's another reason not to use RAISERROR and the like, and it's a reason you might choose to RETURN the empty string '' instead of '???' when an exception occurs. In other words, sometimes it's better to show nothing at all than to show big scary stuff.

  • Exception logging is only performed in "debugging mode"; i.e. when SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON' is in effect for the current connection.

  • And yes, all this talk about exception handling is overkill for a function this small, this simple... or is it? What about future enhancements? What about copying-and-modifying this function to do something more difficult, like return 'fifty five dollars and 32 cents'?
The code itself is written primarily to be readable. The second goal is correctness, and in third place comes conciseness. All three goals have been achieved, as opposed to the code samples here (less readable, less concise... including one provided by me) and the to_money function here (less readable, less concise, not correct...
  • to_money ( -123.45 ) returns '$-,123.45'

  • to_money ( 0 ) returns '$0' instead of '$0.00' in SQL Anywhere 9, whereas to_money ( 0.1 ) returns '$0.10'

  • to_money ( 0 ) fails in versions 10 and 11: Function 'substr' has invalid parameter '3' ('-1')
Putting readability first improves the chances of achieving the second goal, correctness... both during initial development and during later maintenance. If code is easy to read, it's easy to fix; use the "telephone test".

Conciseness can be important if verbosity is standing in the way of readability. Also, the search for conciseness can be a good learning exercise... it certainly was, here, for me.

Here's the code, followed by some comments about how it works:
CREATE FUNCTION dollar_number_string (
IN amount DECIMAL ( 30, 2 ) )
RETURNS VARCHAR ( 50 )
BEGIN

DECLARE string_amount VARCHAR ( 50 );
DECLARE insertion_point INTEGER;

DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );

IF amount IS NULL THEN
RETURN NULL
END IF;

SET string_amount = STRING (
IF amount BETWEEN -.99 AND -.01
OR amount BETWEEN .01 AND .99
THEN '0'
ELSE ''
ENDIF,
ABS ( amount ) );

SET insertion_point = LENGTH ( string_amount ) - 6;

WHILE insertion_point > 0 LOOP

SET string_amount = INSERTSTR (
insertion_point, string_amount, ',' );

SET insertion_point = insertion_point - 3;

END LOOP;

RETURN STRING (
IF amount < 0.00
THEN '-'
ELSE ''
ENDIF,
'$',
string_amount );

EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'EXCEPTION in dollar_number_string at ',
CURRENT TIMESTAMP,
': SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE DEBUG ONLY;
RETURN '???';

END;
Here are some comments about the code:
  • The first SET string_amount strips the sign from the amount and converts it to a string.

  • The string conversion process automatically inserts a period and pads to the right with zeros if necessary; e.g., SELECT STRING ( CAST ( 123 AS DECIMAL ( 30, 2 ) ) ) is '123.00'.

  • The first SET also handles a quirk of the string conversion process whereby 0 is converted to '0.00' but .1 is converted to '.10' (no leading zero).

  • The SET insertion_point = LENGTH ( string_amount ) - 6 calculates the point after which the first comma must be inserted.

  • The WHILE loop inserts commas from right to left instead of left to right so the dynamically changing string length doesn't affect the calculation of insertion_point. This backward looping is an old trick that often makes string manipulation easier.

  • The WHILE loop stops when the insertion point falls off the front of the string; in fact, the loop may not even run at all.

  • The RETURN STRING adds the '$' to the result, plus a leading '-' if necessary.
Note that dollar_number_string supports 30 digits of precision. That number is arbitrary, but important... change it, and you'll be changing some other "magic numbers" in the code.

What's a magic number? It's an Unnamed numerical constant. Magic numbers are generally regarded as bad things. So sue me.

Seriously, the code would be more readable if these magic numbers replaced by named variables; i.e., use "rightmost_insertion_point_offset" for the 6 and "insertion_point_decrement" for the 3.

Here is the code for the test cases shown earlier:
BEGIN
SELECT *
FROM (
SELECT CAST ( NULL AS VARCHAR ( 100 ) ) AS "input",
dollar_number_string ( NULL ) AS "output"
UNION ALL
SELECT '-0', dollar_number_string ( -0 )
UNION ALL
SELECT '-.1', dollar_number_string ( -.1 )
UNION ALL
SELECT '-.99', dollar_number_string ( -.99 )
UNION ALL
SELECT '-.994', dollar_number_string ( -.994 )
UNION ALL
SELECT '-.995', dollar_number_string ( -.995 )
UNION ALL
SELECT '-1', dollar_number_string ( -1)
UNION ALL
SELECT '-1.1', dollar_number_string ( -1.1 )
UNION ALL
SELECT '-23.45', dollar_number_string ( -23.45 )
UNION ALL
SELECT '-123.45', dollar_number_string ( -123.45 )
UNION ALL
SELECT '-9123.45', dollar_number_string ( -9123.45 )
UNION ALL
SELECT '-99123.45', dollar_number_string ( -99123.45 )
UNION ALL
SELECT '-999123.45', dollar_number_string ( -999123.45 )
UNION ALL
SELECT '-9999123.45', dollar_number_string ( -9999123.45 )
UNION ALL
SELECT '-123456789012345678.90',
dollar_number_string ( -123456789012345678.90 )
UNION ALL
SELECT '-1234567890123456789012345678.90',
dollar_number_string ( -1234567890123456789012345678.90 )
UNION ALL
SELECT '-9999999999999999999999999999.99',
dollar_number_string ( -9999999999999999999999999999.99 )
UNION ALL
SELECT '0', dollar_number_string ( 0 )
UNION ALL
SELECT '.1', dollar_number_string ( .1 )
UNION ALL
SELECT '.99', dollar_number_string ( .99 )
UNION ALL
SELECT '.994', dollar_number_string ( .994 )
UNION ALL
SELECT '.995', dollar_number_string ( .995 )
UNION ALL
SELECT '1.00', dollar_number_string ( 1.00 )
UNION ALL
SELECT '1.1', dollar_number_string ( 1.1 )
UNION ALL
SELECT '23.45', dollar_number_string ( 23.45 )
UNION ALL
SELECT '123.45', dollar_number_string ( 123.45 )
UNION ALL
SELECT '9123.45', dollar_number_string ( 9123.45 )
UNION ALL
SELECT '99123.45', dollar_number_string ( 99123.45 )
UNION ALL
SELECT '999123.45', dollar_number_string ( 999123.45 )
UNION ALL
SELECT '9999123.45', dollar_number_string ( 9999123.45 )
UNION ALL
SELECT '123456789012345678.90',
dollar_number_string ( 123456789012345678.90 )
UNION ALL
SELECT '1234567890123456789012345678.90',
dollar_number_string ( 1234567890123456789012345678.90 )
UNION ALL
SELECT '9999999999999999999999999999.99',
dollar_number_string ( 9999999999999999999999999999.99 )
) AS testing
ORDER BY CAST ( "input" as DOUBLE );
END;
Finally, for the sake of comparing apples-to-apples as far as conciseness goes, here's the same function re-coded using an ultra-abbreviated style:



No, I'm not saying that's good style... in fact, it sucks when used for large blocks of code. The point is: yes, the code's concise.

Saturday, January 17, 2009

Culling The Blog List

I personally vouch for the "blog list" at the right hand side of this page. I can say that because I personally use it, and that means it is a continuous work in progress.

And THAT means stale blogs get regularly culled to keep the list both fresh and manageable.

This is not a value judgment about the authors. They have better things to do: day jobs, getting married, raising children, milking cows, and so forth.

All very commendable.

But... Content Is King, and blogs without regular postings ain't gonna get promoted here.

Tuesday, January 13, 2009

Hot Stock Tip?

Is "Sybase" a hot stock tip?

A long-term investor might regard cash-under-the-mattress a better choice than SY ...no splits, no dividends, and after the Client Server Fun Times of the early 1990s, a price history not unlike army life: months of stifling boredom punctuated by moments of sheer terror.



But... in the short term... maybe Sybase looks good. Richard Moroney at Forbes.com certainly thinks so:

Unlike most U.S. companies, Sybase (nyse: SY - news - people) has seen its consensus profit estimates for both 2008 and 2009 climb in the past two months. The 2008 consensus profit, at $2.03 per share, falls in the middle of Sybase's guidance and represents growth of 20%. Wall Street expects profits to rise 6% in 2009, to $2.16 per share. Even based on the low estimate for 2009, $1.95, the stock appears cheap relative to historical norms. The company will offer 2009 guidance this month.

The company is capitalizing on growth in wireless data. For example, more managers are using mobile devices to access their desktops and make business decisions off-site, and Sybase remains at the forefront of this technology. Customers in the financial sector make up 20% of sales, but Sybase says their impact on operations has been minor. However, overlapping revenue from merging companies could potentially lower volume. Sybase, with more cash than long-term debt, is a "best buy."

Thursday, January 1, 2009

Happy New Year To You!



Happy New Year To You!
Happy New Year To You!
Happy New Year Dear Reader,
Happy New Year To You!

- Julie and Jason The Singing Beagles

My workspace contains the usual desk and shelves and sit'n'spin, but it also has a big comfy chair that's perfect for periods of deep contemplation (a.k.a. nap time).

Trouble is, no creature on earth spends more time napping than beagles, and as the picture shows, that's where Julie (front) and Jason spend their days.

With breaks for food, and chasing squirrels... and yawning, er, singing, "Happy New Year" for you.