Blurb: Nick Lomb’s Transit of Venus

Nick Lomb’s Transit of Venus 1631 to the Present is the best illustrated astronomy book for general readers since Terence Dickinson and Alan Dyer’s The Backyard Astronomer’s Guide.  Everything about Lomb’s book from its eye seizing cover, rarely seen historic photographs and charming well researched commentary is first class. Transit is the type of work you steal[1] from and frankly, there is no better endorsement than that.  I’m not the only reader to reach this conclusion check out this and this and this.

When prowling our few remaining bookstores I often skip illustrated works. Usually they’re dumbed-down rehashes of familiar material but, in Transit’s case, I learned something on my first randomly browsed page. The chapter introduction for Venus of the South Seas reads:

Sometimes scientific expeditions have unintended consequences. The desirability of observing the 1769 transit from the South Seas began a chain of events that would lead to the founding of the colony of New South Wales by the British in January 1788. In effect, modern Australia owes its existence to a celestial event.

How about that history haters. I knew why astronomers cared about transits of Venus. In 1677 Edmond Halley, of Halley’s Comet fame, described a method for calculating the astronomical unit from transit of Venus timings. Venus is close enough to the Earth that its track over the Sun differs for widely separated terrestrial observers. This is the familiar parallax effect.  From this small difference you can determine the astronomical unit and if you know the astronomical unit Kepler’s third law tells you the distance of every planet in the solar system.  This was a huge payoff for 17^th, 18^th and early 19^th century astronomers. This is what got Cook out in the Pacific. It’s a great story and Lomb’s telling is the best you will find.

---

[1] I’ve picked up a few page design ideas.

The Hunger Games: a Libertarian Dead Teenager Movie.

I’ve always enjoyed watching teenagers die. Even when I was a teenager, back in the early Pleistocene, I couldn’t get enough adolescent annihilation. Now that I am a certified, some would say certifiable, drooling old fart boomer I enjoy it even more. Youthful folly: it’s riveting entertainment for the elderly. Given my macabre inclinations I looked forward to the Hunger Games with the same — oh my god, lol, like enthusiasm — of a screeching teenage girl which, oddly, is the movie’s target demographic. Well did the Hunger Games disappoint and, more importantly, is it worth ten bucks? In two words: no and yes.

The world of the Hunger Games is your typical progressive green fascist paradise. The sort of place our global warming alarmists, whale saving eco-warriors, Volt driving poseurs, anti-capitalist philosophers and leader venerating loons would like to live. Panem, the principal nation-state depicted in the Hunger Games, is split into two familiar classes: the haves and the have-nots. The haves live in a gleaming special Capitol city and the have-nots grunge away their pointless little people lives in twelve impoverished out-lying districts. Naked force keeps everything in a nice green line. Sure the people in the Capitol while away their carefree days in an endless who can dress like the gayest circus clown contest while the peons in the outer districts fantasize about bread but sacrifices must be made to manage our carbon footprint.

To break the oppressive tedium of face painting, hair dressing and color coordination every year the rulers of Panem select twenty-four lucky teenage Tributes from the twelve out-lying districts and make them fight to the death in Panem’s version of ultimate survivor: The Hunger Games. There’s some background filler about how the games are a punishment and reminder of a long ago civil war that went badly for the out-lying districts. The games, as one of the smarmy TV announcers played by Stanley Tucci, said, “bring us together.” Yeah, there’s nothing like raw fear and continual humiliation to bring a people together.

Face paint and swashbuckling hot heroine, (played by Jennifer Lawrence), aside the world of the Hunger Games is the most credible Sci-Fi dystopia to emerge in years. The laws of physics hold in this movie! There’s no flying faster than the speed of light, no summoning of magical or supernatural forces and no hinging entire pocahantian plot lines on imaginary Unobtainium. Nothing depicted in the Hunger Games is, as far as we know, impossible. We could build Panem today with off-the-shelf technology. It’s probably lost of the popcorn crowd but the world’s physical plausibility is a powerful frame for the story because this shit has already happened. The mayhem of Hunger Games is no worse than what transpired for centuries in Roman amphitheatres, Aztec ball courts or medieval jousting tournaments. Humans can be gamed to death for the filmiest of reasons. This is the real horror of the film; it’s not much a stretch from where we’ve been, to where we are, to what we might become.

I do not fear imaginary monsters; there are plenty of real ones to worry about and the real monsters in the Hunger Games are with us in our world now. Our biggest monster is our naïve belief that we’ve put all this aside: that no modern democratic state could degenerate into a tidy Panemian tyranny, that liberty and freedom, once achieved, is eternal. I’m not so sanguine; we’re a lot closer to a Panem than you might think. If you handed out firearms to the contestants of your average reality TV show we’d be there: minus the green high-speed maglev trains of course. (Don’t worry there’s a stimulus boondoggle for the maglev trains.) Ask yourself, if we armed the Kardashians and made them fight to death on TV how many would care, how many would be relieved, how many Vegas bets would be made? I suspect most would dress up in their gayest apparel and party like Panemians celebrating another dead teenager.

Turn your Blog into an eBook

If you have worked through the exhausting procedure of converting your blog to LaTeX: see posts (1), (2) and (3), you will be glad to hear that turning your blog into an image free eBook is almost effortless. In this post I will describe how I convert my blog into EPUB and MOBI eBooks.

eBooks how the cool kids are reading

eBook readers like Kindles, Nooks, iPads and many cell phones are optimized for plain old prose. They excel at displaying reflowable text in a variety of fonts, sizes and styles. One eBook reader feature, dear to my old fart eyes, is the ability to increase the size of text.  All eBooks are potentially large print editions. There are other advantages: most readers can store hundreds, if not thousands of books, making them portable libraries. It’s now technically possible to hand a kindergarten student a little tablet that holds every single book he will use from preschool to graduate school. The only obstacle is the rapacious textbook industry and their equally rapacious eBook publishing enablers. But fear not open source man will save the day. The days of overpriced digital goods are over! I will never pay more than a few bucks for an eBook because I can make my own and so can you! Let’s get together and kill off another industry that so has it coming!

PDFs, EPUBs and MOBIs

Native eBook file formats like EPUB and MOBI do not handle complex page layouts well. If your document contains a lot of mathematics, figures and well placed illustrations stick with PDF workflows.[1] You will save yourself and your readers a lot of grief.  But, if your document is a prose masterpiece, a veritable great American novel, then “publishing” it as an EPUB or MOBI is great way to target eBook readers. EPUBs and MOBIs can be compiled from many sources.  I start with the LaTeX files I created for the PDF version of this blog because I hate doing the same boring task twice. By far the most time-consuming part of converting WordPress export XML to LaTeX is editing the pandoc generated *.tex files to resolve figures and fix odd run-together-words and paragraphs. To preserve these edits I use pandoc to convert my edited *.tex to *.markdown files.

Markdown

Markdown is a very simple text oriented format. A markdown file is completely readable exactly the way it is. All you need is a text editor. Even text editors are overkill. You could compose markdown with early 20th century mechanical typewriters; it’s a low tech format for the ages: perfect for prose.

The J verb MarkdownFrLatex [2] calls pandoc and converts my *.tex files to *.markdown. I place my markdown in the directory

c:/pd/blog/wp2epub

and to track changes to my markdown files I GIT this directory. MarkdownFrLatex strips out image inclusions and removes typographic flourishes.  When it succeeds it writes a simple markdown file and when it fails it writes a *.baddown file. Baddown files are *.tex files that contain lstlistings and complex figure environments that are best resolved with manual edits. After removing such problematic LaTeX environments the J verb FixBaddown calls pandoc and turns baddown files into markdown files.

Generating EPUB and MOBI files

When the conversion to markdown is complete I run MainMarkdown to mash all my files into one large markdown file with an eBook header. The eBook header for this blog is:

% Analyze the Data not the Drivel
% John D. Baker

The first few lines of the consolidated bm.markdown file are:

% Analyze the Data not the Drivel
% John D. Baker

#[What’s In it for
Facebook?](http://bakerjd99.wordpress.com/2009/09/05/whats-in-it-for-facebook/)

-------------------------------------------------------------------------------------------------

*Posted: 05 Sep 2009 22:44:50*

[Facebook](http://www.facebook.com) is huge: they brag about a user
count well north of one hundred million. If only 0.5% of their users are
active that’s 500,000 *concurrent users.* How many expensive servers
does it take to support such a load? .....

Generating an EPUB from bm.markdown is a simple matter of opening up your favorite command line shell and issuing the pandoc command:

pandoc -S --epub-cover-image=bmcover.jpg -o bm.epub bm.markdown

You can read the resulting EPUB file bm.epub on any EPUB eBook reader. Here’s a screen shot of bm.epub on my iPhone.

iPhone loaded with my blog

The last step converts bm.epub to bm.mobi. MOBI is a native Kindle format. Pandoc can generate MOBI from bm.markdown but it inexplicably omits a table of contents. No problemo:  I use Calibre to convert bm.epub to bm.mobi. Calibre properly converts the embedded EPUB table of contents to MOBI.  Here’s bm.mobi on a Kindle.

Kindle loaded with my blog

All the “published” versions of this blog are available on the Download this Blog page so please help yourself!

---

[1] LaTeX is usually compiled to PDF making it one of hundreds of PDF workflows.

[2] All the J verbs referenced in this post are in the script TeXfrWpxml.ijs

WordPress to LaTeX with Pandoc and J: Using TeXfrWpxml.ijs (Part 3)

WordPress to LaTeX

In this post I will describe how to use the J script TeXfrWpxml.ijs to generate LaTeX source from WordPress export XML.  I am assuming you have worked through (Part 1) and (Part 2) and have:

1. Successfully installed and tested Pandoc.
2. Installed and tested a version of J.
3. Set up appropriate directories (Part 2).
4. Know how to use LaTeX.

Item #4 is a big if.  Inexperienced LaTeX users will probably not enjoy a lot of success with this procedure as the source generated by TeXfrWpxml.ijs requires manual edits to produce good results.  However, if you’re not a LaTeX guru, do not get discouraged. It’s not difficult to create blog documents like bm.pdf.

Step 1: download WordPress Export XML

How to download WordPress export XML is described here.  Basically you go to your blog’s dashboard, select Tools, choose Export  and select the All content option.

Tools > Export > All Content

When you press the Download Export File  button your browser will download a single XML file that contains all your posts and comments. Remember where you save this file. I put my export XML here.

c:/pd/blog/wordpress/analyzethedatanotthedrivel.wordpress.xml

Step 2: download TeXfrWpxml.ijs

Download TeXfrWpxml.ijs and remember where you save it.  I put this script here.

c:/pd/blog/TeXfrWpxml.ijs

Step 3: start J and load TeXfrWpxml.ijs

TeXfrWpxml.ijs was generated from JOD dictionaries. With JOD it’s easy to capture root word dependencies and produce complete standalone scripts. TeXfrWpxml.ijs needs only the standard J load profile to run.  It does not require any libraries or external references and should run on all Windows and Linux versions of J after 6.01.  Loading this script is a simple matter of executing:

load 'c:/pd/blog/TeXfrWpxml.ijs'

The following shows this script running in a J 7.01 console. The console is the most stripped down J runtime.

Step 4: review directories and necessary LaTeX files

The conversion script assumes proper directories are available up: see Part 2. The first time you run TeXfrWpxml.ijs it’s a good idea to check that the directories and files the script is expecting are the ones you want to process.  You can verify the settings by displaying TEXFRWPDIR, TEXINCLUSIONS, TEXROOTFILE and TEXPREAMBLE.

  TEXPREAMBLE
bmamble.tex
  TEXFRWPDIR
c:/pd/blog/wp2latex/
  TEXINCLUSIONS
inclusions
  TEXROOTFILE
bm.tex
  TEXPREAMBLE
bmamble.tex

If all these directories and files exist go to step (5).

Step 5: make sure you are online

The first time you run the converter it will attempt to download all the images referenced in your blog. This is where wget.exe gets executed.  Obviously to download anything you must be connected to the Internet.

Step 6: run LatexFrWordpress

Run the verb LatexFrWordpress.  The monadic version of this verb takes a single argument: the complete path and file name of the export XML file you downloaded in step (1).

xml=: 'c:/pd/blog/wordpress/analyzethedatanotthedrivel.wordpress.xml'

LatexFrWordpress xml

As the verb runs you will see output like:

   LatexFrWordpress xml
What's In it for Facebook?
downloading: c:/pd/blog/wp2latex/inclusions/demotivational-posters-facebook-you.jpg
1 downloaded; 0 not downloaded; 0 skipped
Fake Programming
downloading: c:/pd/blog/wp2latex/inclusions/672169130_vajvn-M.png
1 downloaded; 0 not downloaded; 0 skipped
Laws or Suggestions
downloading: c:/pd/blog/wp2latex/inclusions/i-B5mfdRF-M.jpg
1 downloaded; 0 not downloaded; 0 skipped
Lens Lust

... many lines omitted ...

downloading: c:/pd/blog/wp2latex/inclusions/i-mNK4RHL-M.png
1 downloaded; 0 not downloaded; 0 skipped
WordPress to LaTeX with Pandoc and J: LaTeX Directories (Part 2)
0 downloaded; 0 not downloaded; 1 skipped
+-++
|1||
+-++

When the verb terminates you should have a directory c:/pd/blog/wp2latex full of *.tex files:  one file for each blog post. Now the hard work starts.

Step 7: editing LaTeX posts

The conversion from WordPress XML to LaTeX produces files that require manual edits. The more images, video, tables and other elements in your posts the more demanding these edits will become.  My blog has about one image per post.  Most of these images are wrapped by text. LaTeX has a mind of its own when it comes to floating figures and getting illustrations to behave requires far more parameter tweaking than it should. This is a longstanding weakness of LaTeX that pretty much everyone bitches about. My advice is start at the front of your document and work through it post by post. The files generated by LatexFrWordpress do not attempt to place figures for you but they do bolt in ready-made figure templates as comments that you can experiment with.  Each post file is also set up for separate LaTeX compilation. You don’t have to compile your entire blog to tweak one post. The one good thing about this edit step is once you have sorted out your old posts you do not have to revisit them unless you make major global document changes. The next time you run LatexFrWordpress it will only bring down new posts and images.

Step 8: compile your LaTeX blog

I use batch files and shell scripts to drive LaTeX compilations.  I processed my blog with this batch file.

echo off
rem process blog posting (bm.tex) root file
title Running Blog Master/LaTeX ...

rem first pass for aux file needed by bibtex
lualatex bm

rem generate/reset bbl file
bibtex bm
makeindex bm

rem resolve all internal references - may
rem comment out when debugging entire document
lualatex bm
lualatex bm

rem display pdf - point to prefered PDF reader
title Blog Master/LaTeX complete displaying PDF ...
"C:\Program Files\SumatraPDF\SumatraPDF.exe" bm.pdf

The presence of Unicode APL, see this post, forced me to use lualatex. I needed some very nonstandard APL fonts.  See bm.pdf — also available on the Download this Blog page — to judge the effectiveness of my edits. Producing nice figure laden typeset blog documents is work but, as I will describe in the next post, producing image free eBooks is a simple and far less laborious variation on this process.

WordPress to LaTeX with Pandoc and J: LaTeX Directories (Part 2)

WordPress to LaTeX

In this post I will describe the LaTeX directory structure the J script TeXfrWpxml.ijs is expecting. To convert WordPress export XML to LaTeX with this script you will have to set up similar directories.

LaTeX documents are built from *.tex[1] files. This makes LaTeX more like a compiled programming language than a word processing program. There are advantages and disadvantages to the LaTeX way. In LaTeX’s favor, the system is enormously adaptable, versatile and powerful. There is very little that LaTeX/TeX and associates cannot do.  Unfortunately, “with great power comes great responsibility.” LaTeX is demanding! You have to study LaTeX like any other programming language. It’s not for everyone but for experienced users it’s the best way to produce documents with the highest typographic standards.

LaTeX directory structure

To use LaTeX efficiently it’s wise to pick a document directory structure and stick with it. I use a simple directory layout. Each document has a root directory. The root directory used by TeXfrWpxml.ijs is:

Windows	c:/pd/blog/wp2latex
Linux	/home/john/pd/blog/wp2latex

I put my document specific *.tex, *.bib, *.sty and other LaTeX/TeX files in the root. To handle graphics I create an immediate subdirectory called inclusions.

c:/pd/blog/wp2latex/inclusions

The inclusions directory holds the document’s *.png, *.jpg, *.pdf, *.eps and other graphics files.  To reference files in the inclusions directory with the standard LaTeX graphicx package insert

\usepackage{color,graphicx,subfigure,sidecap}
\graphicspath{{./inclusions/}}

in your preamble. Finally, to track document changes I create a GIT repository in the root directory.

c:/pd/blog/wp2latex/.git

Self contained directories

I take care to keep my document directories self-contained. Zipping up the root and inclusions directory collects all the document’s files. This means that I sometimes have to copy files that are used in more than one document. Many LaTeX users maintain a common directory for such files but I’ve found that common directories complicate moving documents around. You’re always forgetting something in the damn common directory or you are copying a buttload of mostly irrelevant files from one big confusing common directory to another.

TeXfrWpxml.ijs files

The TeXfrWpxml.ijs script searches for these files in the root directory.

bm.tex	Main LaTeX root file
bmamble.tex	LaTeX preamble

bm.tex references bmtitlepage.tex.  I prefer a separate title page file; simply comment out this file if you create titles in other ways. The zip file wp2latex.zip contains a test directory in the format expected by TeXfrWpxml.ijs.  It also has a subset of my blog posts already converted to LaTeX. To get ready for WordPress to LaTeX with Pandoc and J: Using TeXfrWpxml.ijs (Part 3) download wp2latex.zip and attempt to compile bm.tex.  You might have to download a number of LaTeX packages.  Once you have successfully compiled bm.tex you are ready for the next step.

---

[1] LaTeX uses many other file types but key files are usually *.tex files.

WordPress to LaTeX with Pandoc and J: Prerequisites (Part 1)

There are no quick WordPress to LaTeX fixes

WordPress to LaTeX

Over the next three posts I will describe how to convert WordPress’s export XML to LaTeX source code.  I know that many of you are looking for a quick WordPress to LaTeX fix; unfortunately there are no quick fixes. The two formats come from different worlds and are used in different ways.  Producing useful LaTeX source from WordPress export XML will require manual edits.  My goal here is to minimize manual edits, produce high quality LaTeX source and to outline what you will have to contend with. To get an idea of what you can expect download the LaTeX compiled version of this post.

Visual and Logical composition

WordPress and LaTeX are examples of the two basic approaches, visual and logical, taken by writing software.  Visual systems value appearance. It matters what things look like and no effort is spared to get the right look. Logical systems value content. What’s said is far more important than what it looks like. Logical systems impose order and structure and typically defer visual elements.  As you might expect there is no such thing as a pure visual or logical writing system. Successful systems use both approaches to a greater or lesser degree. Composing WordPress blog posts is roughly 35% visual and 65% logical.[1]  LaTeX composition is about 10% visual and 90% logical. The numbers do not line up; there is a basic mismatch here.

Many format X to LaTeX converters tackle this mismatch by attempting to maintain visual fidelity. This is a catastrophic error that renders the entire conversion useless.  Here’s a hint. If you’re using a predominantly logical system like LaTeX you don’t give a rodent’s posterior about visual fidelity. This method dispenses with all but the most basic of visual elements. No attempt is made to preserve fonts, type sizes, image scale, justification, hyphenation, text color and so forth.  The goal is to produce working LaTeX source that can be transformed to whatever final layout the author desires.

Prerequisite Software

I use two programs to transform WordPress export XML to LaTeX:  the J programming language and John MacFarlane’s Pandoc.  Pandoc is an excellent text mark-up to mark-up converter.  It wisely avoids attempting to convert entire complex documents and focuses on getting parts of documents right.  It does a particularly good job of converting HTML to LaTeX which is a crucial part of this process.  I use Pandoc to transform the HTML embedded in WordPress export XML CDATA elements to *.tex files and I use J to preprocess and post process Pandoc inputs and outputs and to stitch everything together into a set of LaTeX ready files.

Download Pandoc from here. I use the Windows command line version. There are Linux and Mac versions as well. Download J from here.  The easiest J install is the 32 bit Windows J 6.02 version. Other versions require additional steps to configure and deploy. If you are already a J user there is no need to install a particular system but you will need:

1. The task library require 'task'
2. The utility program wget.exe

Both of these components are typically part of the J distribution.

Install and check prerequisites

To continue download and install Pandoc and J and run the following tests; if you succeed you’re system is ready for WordPress to LaTeX with Pandoc and J: LaTeX Directories (Part 2).

Pandoc Test:

Download the test file: cdata.html and run Pandoc from the command line:

pandoc –o cdata.tex cdata.html

cdata.html is an example of the HTML code you find in WordPress export XML CDATA elements.  Note: required files are also available in the files sidebar in the WordPress to LaTeX directory.

J Test:

Start a J session and enter the following commands:

require 'task'

shell 'wget –help'

shell 'wget http://conceptcontrol.smugmug.com/photos/i-mNK4RHL/0/L/i-mNK4RHL-L.png'

If the shell command is properly loaded and wget.exe is found you will see help text. The second shell command downloads an image file.  Downloading post images is part of the overall conversion process.

---

[1] Actually this is not bad. Page layout systems are far worse. A typical layout system might be 90% visual and 10% logical making layout systems polar opposites of LaTeX.

Mike Brown Punts Pluto

As a longtime amateur astronomer I appreciate good science writing and Mike Brown’s little book How I Killed Pluto and Why It Had It Coming is a wonderful example of the genre. When Pluto was tossed from the pantheon of planets I didn’t care. I knew that in previous centuries, when asteroids were first discovered, that they were briefly counted as planets. Eventually asteroids lost their planet status; there were too many of them and they were all dinky compared to real planets. Brown notes this bit of astronomical history by pointing to 19th century textbooks with high planet counts. The same holds for Pluto, Eris, Sedna, Quaoar and all the other known baby ice balls that make up the Kuiper belt. Real planets are massive enough to clear their orbits of crap. By this standard Mars barely qualifies and Pluto does not.

The emotional hysterics that greeted Pluto’s demise are still playing out. Some reviews posted here castigate Brown in terms rightly applied to suicide bombers and Obama voters. When Pluto bulks up and starts bullying its neighbors like all manly planets do we’ll talk until then I’d advise the puerile Pluto partisans to plumb up their pie holes otherwise we’ll have to toss you in the crank bin with the creationists and cold fusion nitwits.

cross posted on Goodreads.

Blogging off for Christmas

J Christmas Wallpaper

I am celebrating Christmas by combining two of my favorite things: the programming language J and the superb image editor Picture Window Pro. The other day the good folks at Digital Light and Color announced 64 bit versions of Picture Window Pro. I was delighted. PWP is my favorite image editor. It’s had 16 bit image support since the cows came home and it simply nukes Photoshop when it comes to flat-out crunching performance.  I was afraid that the good old-fashioned C programmers that had crafted this program were retiring: perhaps overwhelmed by a sea of mediocre consumer oriented overpriced sludge — yes Photoshop Elements I’m talking about you! Fortunately it’s not to be! 64 bit multi-core versions of PWP will dramatically extend the lifetime of PWP for this loyal customer.

Speaking of superb good old-fashioned C programs I must give a shout out to J. I’ve been busy preparing a talk for the up coming J conference so J has been on my mind more than usual. Still all work and no play makes J a dull boy! This morning I rooted around in the J bin directories, sucked some J icons into PWP, and then twiddled transformations to generate some Christmas J monitor wallpaper: download and decorate at will!

New Conan not as Philosophical as Old Conan

Bad news philosophers, our preeminent social critic, our font of wisdom, our modern Socrates has succumbed to the malignant Hollywood poisons of reality TV, celebrity whoring, financial ennui and hopeless incompetent governance. Given the forces arrayed against our philosopher king only Vegas bookies on crack would favor his chances. I knew all this when I sat down to view the latest Conan the Barbarian movie but I let hope trump reason — sound familiar. If I wasn’t a mainly-manly-man I would cry for no longer will we ponder religious dissertations like:

“Crom! I have never prayed to you before. I have no tongue for it. No one, not even you, will remember if we were good men or bad. Why we fought, or why we died. All that matters is that today, two stood against many. That’s what’s important! Valor pleases you, Crom; so grant me this one request. Grant me revenge! And if you do not listen, then to hell with you!“

Or learn about what’s good in life:

My friends that golden Apollonic age is past! Now we must content ourselves with eat, pray, love travesties like:

“I live, I love, I slay, I am content.”

When did Conan the Barbarian decide to come out?

Common Table Expression (CTE) SQLServer Queries with J

I’ve been blogging long enough to observe that your awesome posts are often ignored while your little “one-offs” sometimes strike hit gold. This is particularly true for “code example” posts. When I’m trolling for code — the geek equivalent of trolling for babes — I don’t want to read about the author’s programming philosophy or what the hell he thinks about the idiot in the White House. Just show me the code and shut up!

So, taking my own advice,  the following is an example of a SQLSever Common Table Expression (CTE) query.

WITH cte1
     AS (SELECT c.obj_id                            AS obj_id,
                a.created                           AS creation_date,
                Datediff(DAY, a.created, Getdate()) AS day_cnt
         FROM   [HIST].[dbo].[History] a
                JOIN [HIST].[dbo].[HistorySummary] b
                  ON a.rowid = b.rowid
                JOIN [HIST].[dbo].[ObjectNames] c
                  ON c.obj_name = b.obj_name),
     cte2
     AS (SELECT cte1.obj_id AS obj_id,
                CASE
                  WHEN cte1.day_cnt <= 30 THEN 1
                  ELSE 0
                END         AS d0,
                CASE
                  WHEN ( cte1.day_cnt > 30 )
                       AND ( cte1.day_cnt <= 60 ) THEN 1
                  ELSE 0
                END         AS d1,
                CASE
                  WHEN ( cte1.day_cnt > 60 )
                       AND ( cte1.day_cnt <= 90 ) THEN 1
                  ELSE 0
                END         AS d2,
                CASE
                  WHEN ( cte1.day_cnt > 90 ) THEN 1
                  ELSE 0
                END         AS d3
         FROM   cte1)
SELECT obj_id,
       SUM(d0) AS OneMonth,
       SUM(d1) AS TwoMonths,
       SUM(d2) AS ThreeMonths,
       SUM(d3) AS Overdue
FROM   cte2
GROUP  BY obj_id

CTE queries essentially create temporary virtual tables during query execution. They are similar to nested SQL queries but are easier to code, more general, (see recursive CTE queries), and often perform better than their convoluted equivalents. This example creates two virtual tables cte1 and cte2 that are then used to compute a quick histogram.

You can call CTE queries with the J ODBC interface. The following assumes a SQLServer ODBC connection dsn history.

NB. odbc interface
require 'dd'

NB. read CTE query
HistoryAgeSQL=. read 'c:/temp/HistoryAge.sql'

NB. connect sqlserver database
ch =. ddcon 'dsn=history'

NB. select with CTE query
sh =. HistoryAgeSQL ddsel ch

NB. fetch results
data=. ddfet sh,_1

As a final note it’s worth comparing the SQL CTE histogram code with J equivalents.  The two following J verbs taken from the J wiki, (here and here), compute histograms.

NB. computes histograms uses right open intervals
histogram=:<:@(#/.~)@(i.@#@[ , I.)

NB. variation on (histogram) uses left open intervals
histogram2=:<:@(#/.~)@(i.@>:@#@[ , |.@[ (#@[ - I.) ])

They scale to tens of millions of data points; returning results in a few seconds on my laptop. The SQL CTE, shown above, takes about three seconds running on 180,000 row tables on my employer’s full warp servers.  If I could convince the masses to adopt languages like J a large part of my job would disappear. Fortunately, the world is hostile to terse elegance!