Row Value Expressions Simplify Complex Row Selection

If you like mile-long, messy, obfuscated, hard-to-read SQL, the kind that makes sane people want to cuss and spit on everybody and everything, then this tip is not for you. And please don’t apply for a job in my shop. But if you have better things to do than debug SQL, then I have a simple but clever technique for you.

Suppose the chief bean counter walks into your office and asks for a spreadsheet of general ledger transactions. He wants the current-month transactions for accounts 120, 135, 180, 192, and 198. Here’s how a novice would write the query (except that he might not format it quite so prettily):

select * from glxacts
 where date between '2015-02-01' and '2015-02-28'
   and (account=120 or account=135 or account=180 or
        account=192 or account=198)

And he would get thousands of transactions, which the following few represent:

SEQ   DEPARTMENT  ACCOUNT  DATE           AMOUNT
  1       22         120   2015-02-01      75.76
  2       22         180   2015-02-01      25.02
  3       34         135   2015-02-01      44.33
  4       34         180   2015-02-01      15.69
  5       34         192   2015-02-01       5.20
  6       46         120   2015-02-01      28.22
  7       46         198   2015-02-01      16.27
  8        1         120   2015-02-01      88.99
 10       22         135   2015-02-01      88.99
 11       34         120   2015-02-01      88.99
 12       46         135   2015-02-01      88.99

But you are not a novice, and for that reason you know that you can simplify the query this way:

select * from glxacts
 where date between '2015-02-01' and '2015-02-28'
   and account in (120, 135, 180, 192, 198)

The IN operator really cleaned up the code!

The bean counter needs a refinement of the query. He zeroes in on certain accounts for certain departments. (I’ll keep it to three for simplicity. Assume he wants to know about a lot more.)

• For department 22, account 180.
• For department 34, account 135.
• For department 35, account 198.

You could do this:

select * from glxacts
 where date between '2015-02-01' and '2015-02-28'
   and (   department = 22 and account = 180
        or department = 34 and account = 135
        or department = 46 and account = 198  )

That would give you the correct results.

SEQ   DEPARTMENT  ACCOUNT  DATE           AMOUNT
===   ==========  =======  ==========     ======
  2       22         180   2015-02-01      25.02
  3       34         135   2015-02-01      44.33
  7       46         198   2015-02-01      16.27

But doesn’t it seem a shame to have replaced that handy IN operator with a bunch of ugly AND’s and OR’s? I have good news! You can still use IN when comparing two or more fields!

select * from glxacts
 where date between '2015-02-01' and '2015-02-28'
   and (department, account) in
        (values(22,180), (34,135), (46,198))

Think of the VALUES keyword as a mechanism to create a table on the fly.

Is that fine, or what!?