From: hudek@polaris.llnl.gov (David Hudek)
Newsgroups: comp.databases.oracle
Subject: SQL*Plus Puzzle
Date: 26 Jan 93 18:53:08 GMT
Organization: Lawrence Livermore National Labs, Livermore CA
Originator: hudek@roxanne.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@llnl.gov

<disclaimer:  These are personal opinions only. >
< I do not speak for any group or organization. >