SQL*Plus Puzzle

From: David Hudek <hudek_at_polaris.llnl.gov>
Date: 26 Jan 93 18:53:08 GMT
Message-ID: <147754_at_lll-winken.LLNL.GOV>

<||>

Here's a puzzle of sorts.

I was asked to create a little report based on a pre-existing table. After some thought, I decided to do it in C using Pro*C, but since my background is more from underlying systems software than applications (I'm still fairly new at the database game), I wondered if I was suffering from the "when you have a hammer, all problems look like a nail" syndrome and used C when a clever SQL/SQL*Plus solution might have been more appropriate. I've already written the code with Pro*C and it works fine, so I probably won't change it, but for grins (and the opportunity to learn from the "gurus" out there) can anyone out there think of a clean way to do the following using SQL*Plus?

Relevant columns (names changed to protect the innocent) are:

	aThingAMaJig	NOT NULL	CHAR(12)
	aDeelyBopper	NOT NULL	NUMBER
	aFlag		NOT NULL	CHAR(1)

aThingAMaJig, though a char, holds numbers which have the pattern:
	{MajorNumber}{MinorNumber}
		where MajorNumber is > 0 and generally two-digit ASCII with
		leading zero (e.g., "01" to "99")
		and MinorNumber is > 0 ASCII up to 10 digits long, but
		by convention is usually two-digits with leading zero
		To confuse matters, unfortunately, the Major Number in
		one instance took the form {MajorNumber}/{MajorNumber}
		(a la "05/06") which also restricted its MinorNumber to 
		7 digits.
		examples of valid aThingAMaJig's are:
		"0101", "0102", "05/0603", "05/0606"

aDeelyBopper is a number > 0
aFlag is a single character that usually holds upper-case letters

What is desired is for the user to input a MajorNumber and to get back a report concerning all aDeelyBoppers that have that MajorNumber in their corresponding aThingAMaJig and also have aFlag of, let's say, "X"; the report should list the ranges of *consecutive* aDeelyBoppers that satisfy the criteria, grouped by aThingAMaJigs, a count of each range, and a grand total of all.
The users wanted the format to look something like this:

       Date BlahBlah MajorNumber Title

       (aThingAMaJig)	Range Of Blah (aDeelyBoppers)	(# this range)
       -----------	---------------------------	--------------
        05/0601         001501 - 001800         	300

        05/0602         000151 - 000175         	25
                        000951 - 001100         	150

        05/0603         000401 - 000525         	125
                        000776 - 000825         	50
                        000876 - 001175         	300
                        001551 - 001800         	250

        05/0604         000351 - 000400         	50
                        000551 - 000600         	50

        05/0605         000801 - 001800         	1000
                                                	-------------------
                                                	2300  - Grand Total
                                                	-------------------

In this case, showing the case of the {MajorNumber}/{MajorNumber}{MinorNumber} form of aThingAMaJig. The top of the page has the date, and a title containing the MajorNumber they input. The first column of the output only prints out aThingAMaJig when it's different from the previous, and skips a line when it does print. The second column contains the range of consecutive aDeelyBoppers that correspond to the aThingAMaJig and have aFlag "X". The third column lists the number of aDeelyBoppers in each range and at the end has the grand total of all aDeelyBoppers that have aFlag "X" and a aThingAMaJig that contains the specified MajorNumber.

Is there an easy way to do this using SQL*Plus? I got hung up on the requirement to list the ranges of *consecutive* aDeelyBoppers and decided it would be easy to just do it in C using Pro*C with a CURSOR declared as

	SELECT		aThingAMaJig, aDeelyBopper, aFlag
	FROM		theTable
	WHERE			(aThingAMaJig LIKE :whatMajorNum||'%')
			AND	(aFlag = 'X')
	ORDER BY	aThingAMaJig, aDeelyBopper;

and just merrily fetch along, detecting changes in aThingAMaJig and/or non-consecutive aDeelyBoppers, keeping a couple running sums, and printing out as appropriate. (with a little special processing for the unusual 05 or 06 major number case).

It works fine as a C/Pro*C, but I wonder at its efficiency, fetching all those rows just to do comparisons, etc. that the database system should do for you. Is there a clean way to do it in SQL*Plus? It seems like there ought to be, using mins and maxes and carefully worded clauses, but being rushed for time, I went with a way I knew would work. Any "gurus" out there wish to share a better way? :-)

Thanks in advance,
dave hudek
djh_at_llnl.gov

<disclaimer: These are personal opinions only. > < I do not speak for any group or organization. > Received on Tue Jan 26 1993 - 19:53:08 CET