## Help Wanted: Report Generator

### May 11, 2018

Regular readers of this blog know that in my day job I am a programmer charged with maintaining an enterprise-wide database system, running on HP-UX (we’re switching to Linux next year) and Oracle 12g. We are in the process of replacing our existing report-writing software with a new, user-friendly product. Personally, I think it’s crazy to train non-technical people how to join tables and produce aggregates (the very first example in the training manual tripled my age to 183 because a one-to-many join was done incorrectly), but the users are excited because they will be able to get their data themselves instead of relying on the IT department to produce reports. I won’t tell you which reporting software we bought (you would recognize the name) or how much it cost (you will cry).

Some of the reports that we produce (probably a quarter to a third of them) can’t be done using the new software because the necessary fields aren’t in the data warehouse. The IT department (I am one of six programmers) will have to rewrite those reports using some combination of Oracle SQL*Plus and PL/SQL; the result will be plain-ascii reports delivered as LIS files in our standard enterprise software. Yes, 1960s-era reports, 132 columns by 66 rows per page, printed on a modern laser printer instead of green-bar fan-fold paper — that’s called progress where I work.

I’ve been looking at report-writing software. SQL*Plus actually isn’t bad — it must have been written in the 1960s for 1960s-era reports — but I still have to count the print columns to specify the linesize. SQL*Plus provides report headers and footers, page headers and footers that can include values drawn from the data, and control-breaks with record counts and totals. The one feature that is missing is proper headers and footers on each control-break; the field that caused the break must be included in the body of the report (on each detail line) rather than on a break header (so it takes horizontal space on the report, which may be in short supply), and there is no provision to include the break value in the total line of the break.

So I am in the market for a simple report generator. It must work with Oracle and produce plain-ascii output. Features include reprt headers and footers, page headers and footers, break headers and footers, all of which must be able to include values from the output. It has to be callable from a Unix shell script so it will fit with our enterprise system.

I’ve been looking for something suitable, and surprised I can’t find it. All of the solutions I have found are some combination of expensive, overly feature-full, or hard to use. I don’t want to use MS-Access with an ODBC connection. I looked at Jasper and BIRT; neither is suitable. I looked at every old Unix book in my library; I figured Don Libes would have something in his useful book, but he doesn’t. I’m looking for something at the level of a Unix shell script, something like Awk but with more of a report-writing flavor. On the next page you will see a sample report specification for a real report that I will need to write; SQL*Plus can handle most of that, but not the breakheader and breakfooter elements.

So I come to my readers and ask for help. Does anyone know of a simple 1960s-era Unix report generator?

Maybe I’ll write one.

Many thanks.

## An Array Exercise

### May 8, 2018

We worked on linked lists in the previous exercise, so today we will work on arrays:

Given an array of distinct integers, replace each element of the array with its corresponding rank in the array. For instance, the input array [10,8,15,12,6,20,1] is replaced by the output array [4,3,6,5,2,7,1] because element 1 has rank 1, element 6 has rank 2, element 8 has rank 3, element 10 has rank 4, element 12 has rank 5, element 15 has rank 6, and element 20 has rank 7.

Your task is to write a program to replace array elements by their rank. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Linked List Exercises

### May 4, 2018

I like to do exercises on linked lists. In languages like C, linked lists provide good drill for students who are uncertain about structures and pointers; in any language, lists provide good drill on recursion. Today’s exercise is about linked lists; we’ve seen some of these before, but it’s good to review:

- Take a list of integers and rearrange it so all the even integers appear before all the odd integers, with both evens and odds appearing in the output in the same order as the input.
- Take a list of integers, split it into two lists each containing alternate elements from the input list, then join the two lists back together.
- Take a list of integers and rearrange it so alternate nodes are each greater than their two adjacent nodes; in other words, the integers are in alternating high-low order.

Your task is to perform the three linked list exercises described above. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Gauss’s Criterion

### May 1, 2018

Yesterday was the 241st anniversary of the birth of Carl Gauss, who is perhaps the greatest mathematician who ever lived. In his honor, I picked a small task based on some mathematics that he discovered. Gauss’s Criterion states:

Let

pbe an odd prime andba positive integer not divisible byp. Then for each positive integer 2k− 1 <p, letrbe_{k}r≡ ( 2_{k}k− 1)b(modp) with 0 <r<_{k}p, and lettbe the number of evenrs. Then (_{k}b/p) = (-1)^{t}, where (b/p) is the Legendre symbol.

Your task is to write a program to compute Gauss’s criterion, and confirm that it is the appropriate power of the Legendre symbol. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Sum Square Digits Sequence

### April 27, 2018

Regular readers of this blog know of my affinity for recreational mathematics, and today’s exercise is an example of that.

We looked at happy numbers in a previous exercise. Recently, Fermat’s Library re-published a proof by Arthur Porges, first published in the American Mathematical Monthly in 1945, that the trajectory of the sequence of summing the squares of the digits of a number always ends in 1 (a Happy Number) or a set of eight digits 4, 16, 37, 58, 89, 145, 42, 20 (a Sad Number). So, we look at this task again:

You are given a positive integer. Split the number into its base-ten digits, square each digit, and sum the squares. Repeat until you reach 1 (a Happy Number) or enter a loop (a Sad Number). Return the sequence thus generated.

For instance, 19 is a happy number, with sequence 19, 82, 68, 100, 1, while 18 is a sad number, with sequence 18, 65, 61, 37, 58, 89, 145, 42, 20, 4, 16, 37, …

Your task is to compute the sequence described above for a given *n*. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

Today’s exercise is a game that mathematicians play; it comes from /u/HarryPotter5777 on Reddit:

You are given a pair of integers. You make a sequence of steps in which one member of the pair is doubled and the other is incremented by one. Your goal is to find a sequence in which the two members of the pair are equal. For instance, the sequence (1 8), (2 9), (4 10), (5 20), (10 21), (11 42), (22 43), (44 44) ends with the two numbers equal, so (1 8) is a valid starting pair.

The question is whether or not it is always possible to find a sequence, regardless of the starting pair, which makes the two numbers equal. /u/shouldertarget gives a hand-waving explanation that the answer is “Yes” later in the Reddit thread mentioned above.

Your task is to write a program that, given a starting pair, derives a sequence that makes the two numbers equal. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Recognizing Fibonacci Numbers

### April 20, 2018

We have a simple exercise for a Friday afternoon:

Write a program that determines if an input number

nis a Fibonacci number.

Your task is to write a program that determines if a number is a Fibonacci number. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Generating Random Factored Numbers, Easily

### April 17, 2018

Sometimes it is convenient to have large random composite numbers with known factorization, particularly for testing prime number programs. Eric Bach gives a fast but complicated method. Adam Kalai gives a simpler method that’s not so fast:

Input: Integern> 0.

Output: A uniformly random number 1 ≤r≤n.

- Generate a seqneuce
n≥s_{1}≥s_{2}≥ … ≥s= 1 by choosing_{i}s_{1}∈ {1, 2, …,n} ands_{i+1}∈ {1, 2, …s}, until reaching 1._{i}- Let
rbe the product of theprimes‘s._{i}- If
r≤n, outputrwith probabilityr/n.- Otherwise, RESTART.

Your task is to implement Kalai’s method of generating random composite numbers with their factorization. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Third Biggest Number

### April 13, 2018

Today’s exercise is for all the beginning programming students who read this blog:

Write a program to read ten numbers input by the user and write the third largest of those ten numbers.

Your task is to write the program described above. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

## Square Square

### April 10, 2018

We have today an opportunity for some code golf:

Write a program to find a four-digit positive integer that, when multiplied by itself, contains the original four-digit number in the last four digits of the product.

Your task is to write a program to find the desired number. If you wish, you can take it as a challenge to use the minumum number of symbols in your programming language. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.