NetNews Usenet Archive 1993 #3/NN_1993_3.iso/spool/comp/database/oracle/2962

Internet Message Format | 1993-01-28 | 5.0 KB

Path: sparky!uunet!portal!lll-winken!roxanne.llnl.gov!hudek
From: [e-mail] (David Hudek)
Newsgroups: comp.databases.oracle
Subject: SQL*Plus Puzzle
Message-ID: <[e-mail]>
Date: 26 Jan 93 18:53:08 GMT
Sender: [e-mail]
Organization: Lawrence Livermore National Labs, Livermore CA
Lines: 108
Nntp-Posting-Host: roxanne.llnl.gov
Originator: [e-mail]

<||>

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
[e-mail]

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