Re: Impossible SQL query?

From: <BigBoote66_at_hotmail.com>
Date: 3 Oct 2005 13:33:10 -0700
Message-ID: <1128371590.839010.304690@g14g2000cwa.googlegroups.com>

Assume this schema:

alter session set nls_date_format = 'MM/DD/YY';

Create table mytest (id number, Startkey char(1), theDate Date);

insert into mytest values (1, 'B', '1/1/05');
insert into mytest values (2, 'B', '1/2/05');
insert into mytest values (3, 'E', '1/3/05');
insert into mytest values (4, 'B', '1/3/05');
insert into mytest values (5, 'E', '1/4/05');
insert into mytest values (6, 'E', '1/4/05');
insert into mytest values (7, 'E', '1/5/05');
insert into mytest values (8, 'B', '1/6/05');
insert into mytest values (9, 'B', '1/7/05');
insert into mytest values (10, 'E', '1/7/05');

commit;

You can "sort of" do it without PL/SQL. You don't need procedural logic, but you do need an accumulator to keep track of how many "End" records you've thrown out as you work your way through the result set. You can do this by creating a PL/SQL package that holds a variable, and includes a function for incrementing the variable every time we keep an end row:

CREATE OR REPLACE PACKAGE QueryUtility_PKG AS

   End_Rows_Kept NUMBER := 0;

   FUNCTION KeepEndRow(

      TheRowCount   NUMBER
    , TheDate       DATE
   )
      RETURN NUMBER;

END QueryUtility_PKG;
/

CREATE OR REPLACE PACKAGE Body QueryUtility_PKG AS

   FUNCTION KeepEndRow(

      TheRowCount   NUMBER
    , TheDate       DATE
   )
      RETURN NUMBER AS
   BEGIN
      DECLARE
         TheCount         NUMBER;
         TheReturnValue   NUMBER := 0;
      BEGIN
         IF TheRowCount = 1 THEN
            End_Rows_Kept := 0;
         END IF;

         SELECT COUNT(*)
          INTO TheCount
          FROM MyTest
         WHERE StartKey = 'B'
           AND MyTest.theDate < KeepEndRow.theDate;

         IF End_Rows_Kept < TheCount THEN
            TheReturnValue := 1;
         END IF;

         End_Rows_Kept := End_Rows_Kept + TheReturnValue;
         RETURN TheReturnValue;
      END;

   END;
END QueryUtility_PKG;
/

Every time you call the KeepEndRow function, you'll increment the counter if the number of "Begin" rows is greater than the number of "End" rows you've kept so far (kept track of in the "counter"). We use the RowNum argument to initialize the counter to 0, so that the variable is clean at the beginning of any query that uses this function.

Once that function exists, you can run your query thusly:

SELECT mt1.StartKey || mt1.ID "Begin"

     , mt2.StartKey || mt2.ID "End"
 FROM (SELECT ROWNUM TheRowNum

            , t.*
        FROM (SELECT ID


                   , StartKey

                   , theDate

               FROM MyTest
              WHERE StartKey = 'B'
              ORDER BY theDate) t) mt1
    , (SELECT ROWNUM theRowNum
            , t2.*
        FROM (SELECT t.*


                   , QueryUtility_PKG.KeepEndRow(ROWNUM, t.theDate)

KeepRow
               FROM (SELECT ID
                          , StartKey
                          , theDate
                      FROM MyTest
                     WHERE StartKey = 'E'
                     ORDER BY theDate) t) t2
       WHERE KeepRow = 1) mt2

WHERE mt1.theRowNum = mt2.theRowNum(+)
ORDER BY mt1.theRowNum;

We're basically creating two tables - the set of Begins and the set of non-discarded Ends. We then just join them up based on row number determined by date order.

-Steve Received on Mon Oct 03 2005 - 15:33:10 CDT