Re: Can you do this in SQL

[rob@xxxxxxxxx]

I strongly suggest you try the cursor method.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin



                    "Alan Novick"
                    <mida@pressmantoy.c       To:     <midrange-l@midrange.com>
                    om>                       cc:
                    Sent by:                  Fax to:
                    midrange-l-admin@mi       Subject:     Re: Can you do this 
in SQL
                    drange.com


                    01/31/2002 09:50 AM
                    Please respond to
                    midrange-l






Rob,

I used Alexei's first solution, the one with one sql statement.  I think it
chugged through over 3 million times for a 6800 record file.  Totally blew
away message SQL7957 that tells you how many records were updated which
reported the number as a blank.  Ran that thing at a low priority which I
use for most of my SQL interactive processing.


David,

Thank you for taking the time to compare the OPNQRYF and SQL performance
times.  I know when embedded SQL first came out my experience with it was
that it was much slower and I had heard that this was solved but did not
realize they were now the same.  I like OPNQRYF because it separates out
the
database manipulation, lets me still use my control indicators, and makes
the RPG program much easier to debug.  On the other hand, I cannot imagine
programming life without SQL.


----- Original Message -----
From: <rob@dekko.com>
To: <midrange-l@midrange.com>
Sent: Wednesday, January 30, 2002 4:12 PM
Subject: Re: Can you do this in SQL



Alan,

Which solution of Alexei's did you try?  The single SQL statement, or the
Cursor logic?

When I mentioned the part about SETLL before READ I forgot to mention cycle
versus the READ :-)

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin



                    "Alan Novick"
                    <mida@pressmantoy.c       To:
<midrange-l@midrange.com>
                    om>                       cc:
                    Sent by:                  Fax to:
                    midrange-l-admin@mi       Subject:     Re: Can you do
this in SQL
                    drange.com


                    01/30/2002 03:54 PM
                    Please respond to
                    midrange-l






Alexei:

You are right that your solution was not the best from a performance point
of view (yes I tried it) but I really enjoyed it, very impressive.  I use
SQL all the time and it's great to learn something new.  It's definitely
true that RPG is the right solution here but I these kinds of things give
an
idea of what is possible in other situations where it might be appropriate.
This is the best tip since I saw the bicycle icon in David Kahn's sig file!

I've also been taken aback by how the thread developed.  FWIW I'm a big fan
of OPNQRYF too, also since good old realease 8 of CPF.  Of course, when I
write the RPG program I will use the RPG cycle and level break indicators
but I don't want to start a big ruckus on the merits of that style :)

Thanks to all who responded.

Alan Novick
Pressman Toy Corporation
Enjoying the unsual biking weather in New Brunswick, NJ


----- Original Message -----
From: "Alexei Pytel" <pytel@us.ibm.com>
To: <midrange-l@midrange.com>
Sent: Tuesday, January 29, 2002 6:47 PM
Subject: RE: Can you do this in SQL



Just as an exersize - I can think of two ways of doing this in SQL:

1) in a single statement:

      update cus tu set rank =
      ( select count(*) from cus ts
         where tu.yr = ts.yr and tu.money <= ts.money )

which is not the best performer


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.