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 :)...