Re: order by not consistent

[Quoted] Well, that's what I get for showing the easiest example of the more than 30 [Quoted] of these procedures. I agree with you of course that the method you use is [Quoted] more efficient. I realize vsort is a dead variable, in 24 other procedures [Quoted] it's used. This form is overkill for this query but actually works quite well when the cursors run 3 deep and the bind variables are in the double digits. This code sits in the middle between a delphi user interface and a [Quoted] crystal report output, having crystal beat on your production tables is a bad thing performance wise.

Thanks for the response, I'ld still like to know about the order by problem :(
Barry

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D405866.C32DFF11_at_exesolutions.com...
> bgt0990 wrote:
>
> > Oracle entreprise 8.1.7.4
> >
> > I am trying to load output into tables for use by other programs
(crystal
> > reports etc)
> >
> > If I truncate my destination table prior to running the procedure the
output
> > always seems ordered properly 1, 2, 3, 4, 5, etc. However on successive
> > runs the order invariably gets messed up 3,4,5,1,2 it's still
ordered,
> > but the first few (varies greatly) records show up at the end of the
table.
> > Most of my routines are useing ref cursors I wonder if that is part of
the
> > answer.
> >
> > I include some sample code, but I've got some 30 procedures where this
all
> > acts the same.
> >
> > PROCEDURE P_PUNCH_LIST (
> > aKey IN VARCHAR2,
> > C0BatchID IN CHAR,
> > o1Order IN CHAR )
> > AS
> >
> > TYPE rc_type IS REF CURSOR;
> > l_cursor rc_type;
> >
> > CURSOR l_template IS SELECT labno FROM RESULT_MASTER;
> >
> > c1rec l_template%ROWTYPE;
> >
> > SQLSTRING VARCHAR2 (2000);
> > vSort VARCHAR2(10):= 'LABNO';
> >
> > BEGIN
> > DELETE FROM RPT_RESULT_MASTER t WHERE UPPER(t.KEY) = UPPER(aKey);
> > commit;
> > IF o1Order IS NOT NULL THEN
> > vSort:= trim(UPPER(o1Order));
> > END IF;
> >
> > SQLString := 'SELECT distinct labno FROM RESULT_MASTER WHERE
BATCHID =
> > :1';
> > SQLSTRING := sqlstring ||' AND TESTSEQ < 99';
> > SQLSTRING := sqlstring ||' ORDER BY LABNO';
> >
> > OPEN l_cursor FOR SQLString
> > using C0BatchID;
> >
> > LOOP
> > FETCH l_cursor INTO c1rec;
> > EXIT WHEN l_cursor%NOTFOUND;
> >
> > INSERT INTO RPT_RESULT_MASTER
> > (KEY, batchid, labno)
> > VALUES
> > (akey, C0BatchID, c1rec.labno );
> > END LOOP;
> > CLOSE l_cursor;
> > commit;
> > END;
> >
> > Any help would be appreciated
> > Barry
> >
> > -----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> > http://www.newsfeed.com The #1 Newsgroup Service in the World!
> > -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
=-----
>
> Can't answer the question about the ORDER BY but I can tell you that you
have
> chosen one of the least performance and scalability friendly methods of
> accomplishing your goal I can imagine. Perhaps you thing that by passing
in
> o1Order you are altering something. But since you never use vSort for
anything it
> is a dead-end parameter.
>
> Either way ... based on your code all you really need is the following:
>
> BEGIN
> DELETE FROM RPT_RESULT_MASTER t
> WHERE UPPER(t.KEY) = UPPER(aKey);
>
> INSERT INTO RPT_RESULT_MASTER
> SELECT akey, C0BatchID, labno
> FROM result_master
> WHERE batchid = C0BatchID
> AND testseq < 99
> ORDER BY LABNO;
> COMMIT;
> END;
>
> And I am still left wondering why you don't just run your report from the
> RESULT_MASTER table instead of even doing this.
>
> Daniel Morgan
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Jul 25 2002 - 22:15:57 CEST