Re: Tricky query

From: Spencer <spencerp_at_swbell.net>
Date: Fri, 1 Jun 2001 22:52:13 -0500
Message-ID: <boZR6.484$yt.8206@nnrp1.sbc.net>

"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:5g9chtopo1cklr1nbs884jljva0g6gdk3e_at_4ax.com...
> On Thu, 31 May 2001 01:35:57 GMT, willjamu_at_mindspring.com (James
> Williams) wrote:
>
> >On Wed, 30 May 2001 23:49:53 +0800, Dino Hsu <dino1_at_ms1.hinet.net>
> >wrote:
> >
> >Oracle 8i has a ranking function as I was shown this morning. Prior to
> >Oracle 8 I would think some complex PROC or PL/SQL was required.
> >
>
> Yes, I checked the 8.1.7 documentation (SQL Reference), a new type of
> functions called 'analytical functions' are invented, in addition to
> 'user functions' and 'aggregate functions'. These analytical functions
> are particularly useful in OLAP or Data Warehousing applications.
> Before this, what we could do is called 'psudocolumns' such as rownum,
> but it lacks the syntax 'OVER', 'PARTITION BY', 'ORDER BY' as found in
> analytical functions. Moreover, many new functions are introduced,
> including RANK(), RATIO_TO_REPORT(), PERCENT_RANK(), etc.. It seems
> there are some useful improvements from 8 to 8i. I cannot read through
> the whole SQL Reference at the moment, but I believe there still are
> more interesting things to be found in 8i.
>
> Dino
>

prior to the introduction of the new features in 8i, one approach would have been to create a PL/SQL block to handle the req'd logic. and yours is not an overly complex problem.

i haven't tested this, but it should be pretty close. depending on frequently this type of result is needed, and how frequently the data changes, i would consider adding another column to the table, and actually storing the "oldest premise for order" rank value. the PL/SQL block like the one below could be used to crank through the data, and reset values where needed. the SQL to retrieve the "n oldest" becomes simpler and faster.

here's a PL/SQL block that identifies the three oldest...

set serveroutput on

DECLARE
  CURSOR lcsr_oldest IS
  SELECT t.SOURCE_ORDER_NBR As ORDR

       , t.KY_PREM_NO As PREM
    FROM TB_SALES_DETAIL t

   ORDER BY t.SOURCE_ORDER_NBR
          , t.DTE
          , t.TM ;

  li_cnt PLS_INTEGER;
  lrec_curr lcsr_oldest%ROWTYPE;
  lrec_prev lcsr_oldest%ROWTYPE;
BEGIN
  DBMS_OUTPUT.ENABLE(10000);
  OPEN lcsr_oldest;
  FETCH lcsr_oldest INTO lrec;
  IF lcsr_oldest%NOTFOUND THEN
    NULL;
  ELSE
    lrec_prev.ORDR := lrec_curr.ORDR;
    lrec_prev.PREM := NULL;
    LOOP

      EXIT WHEN lcsr_oldest%NOTFOUND;
      IF lrec_prev.ORDR = lrec_curr.ORDR THEN
        -- same order
        IF lrec_prev.PREM = lrec_curr.PREM THEN
          NULL;
        ELSIF li_cnt < 3 THEN
          -- one of the oldest premise
          DBMS_OUTPUT.PUT_LINE(lrec_curr.ORDR
           ||' '||li_cnt||' '||lrec_curr.PREM);
          li_cnt := li_cnt + 1;
        END IF;
      ELSE
        -- new order number
        li_cnt := 0;
        lrec_prev.ORDR := lrec_curr.ORDR;
      END IF;
      lrec_prev.PREM := lrec_curr.PREM;
      FETCH lcsr_oldest INTO lrec_curr;

    END LOOP;
  END IF;
  CLOSE lcsr_oldest;
END; much, much simpler.

a PL/SQL block could be used to crank through the data, and reset any of the ranks that should be changed.

set serveroutput on

DECLARE
  CURSOR lcsr_oldest IS
  SELECT t.SOURCE_ORDER_NBR As ORDR

       , t.KY_PREM_NO As PREM
    FROM TB_SALES_DETAIL t

   ORDER BY t.SOURCE_ORDER_NBR
          , t.DTE
          , t.TM ;

  li_cnt PLS_INTEGER;
  lrec_curr lcsr_oldest%ROWTYPE;
  lrec_prev lcsr_oldest%ROWTYPE;
BEGIN
  DBMS_OUTPUT.ENABLE(10000);
  OPEN lcsr_oldest;
  FETCH lcsr_oldest INTO lrec;
  IF lcsr_oldest%NOTFOUND THEN
    NULL;
  ELSE
    lrec_prev.ORDR := lrec_curr.ORDR;
    lrec_prev.PREM := NULL;
    LOOP

      EXIT WHEN lcsr_oldest%NOTFOUND;
      IF lrec_prev.ORDR = lrec_curr.ORDR THEN
        -- same order
        IF lrec_prev.PREM = lrec_curr.PREM THEN
          NULL;
        ELSIF li_cnt < 3 THEN
          -- one of the oldest premise
          DBMS_OUTPUT.PUT_LINE(lrec_curr.ORDR
           ||' '||li_cnt||' '||lrec_curr.PREM);
          li_cnt := li_cnt + 1;
        END IF;
      ELSE
        -- new order number
        li_cnt := 0;
        lrec_prev.ORDR := lrec_curr.ORDR;
      END IF;
      lrec_prev.PREM := lrec_curr.PREM;
      FETCH lcsr_oldest INTO lrec_curr;

    END LOOP;
  END IF;
  CLOSE lcsr_oldest;
END; Received on Fri Jun 01 2001 - 22:52:13 CDT