RE: RE: RE: Can you do this in SQL -- MIDRANGE-L

> Mike Naughton
>
> (...) but in comparing SQL and OPNQRYF I think SQL wins hands down.

Mike (and Rob, since you voiced a similar sentiment):

I'm GLAD you guys disagree.  There's always something to be learned in good,
honest debate.  For example, Rob's point that subselects are just as painful
in OPNQRYF as SQL is right on target.  My soapbox was a little unstable on
that one <grin>.

SQL definitely beats OPQNRYF on a feature/function basis, but I guess there
really is a division in philosophy between embedded SQL and OPNQRYF/RPG that
has little to do with functionality, and more to do with personal
preference.



> I think the reason you don't see many questions about OPNQRYF is that the
> only people who use it any more are people who know it and are comfortable
> with it -- no one else would go near it unless they were forced to,

But even in the days when it was used, the questions weren't nearly as
complex.  I think this is because OPNQRYF file was used for what SQL is good
at - the QUERY part - without having all the gyrations required for the
update part.  I think that's why I like the separation - use OPNQRYF to
select the records I need and then use RPG to process those records.  Of
course, I'm an old-fashioned logical view guy, so of course this is
comfortable to me.


> But nowadays if I want to write a report that
> allows a user to enter a few selection criteria and pick a sort order  and
> then pulls data from, say, a transaction file and a couple of master
> files, I'll pick embedded SQL over constructing a READ loop with multiple
> CHAINs every time (and I used to do that rather than use OPNQRYF).

This makes sense, Mike.  I use embedded SQL myself, and I think it's a great
tool when it's the right tool.  For reports, especially, or for that matter
for ANY data mining type of operation, the flexibility of SQL is hard to
beat.

<SOAPBOX MODE> As long as the SQL stays on the host, and isn't migrated to a
VB client or some such. </SOAPBOX MODE>


> I think the reason we see so many questions about SQL is that a lot of
> people see its potential and they're trying to learn it.

The problem with this is that some of the people trying to learn it are also
using it to implement busniess solutions, without having a clear idea of
what they're implementing.  Since SQL is one abstraction layer removed from
the database, and does "magic" under the covers, it can be abused.  The
question that prompted this thread is a perfect example of that.  Some of
the "solutions" proposed were horribly bad from a business standpoint, but
if you don't understand how SQL works, you don't see how bad they are.  If
you did the same code in RPG you would immediately realize how bad the
design was.  Unfortunately, a lot of people are looking for quick fix SQL
solutions without wanting to do the homework to find out what these
statements do.  Even worse, people are posting "answers" without any
disclaimers as to how bad the solutions are.  Together, these trends can
make for some really awful code and then our poor box is saddled with a "bad
performance" label, even though a CORRECT solution would scream.


> Is SQL syntax arbitrary and obscure? Well, I think that depends on one's
> point of view and experience (..) Once you get used to it, it makes a lot
> more sense, but that takes time and effort. Personally, I think SQL is
> pretty understandable -- it's certainly possible to write obscure
> statements, but then it's also possible to write obscure RPG programs, so
> I don't really think that counts as a failing. . . .

Again, this is really an opinion issue.  Personally, I think the syntax for
joins and subselects leaves something to be desired.  Then again, I haven't
offered any alternatives other than RPG, so I'm guilty of the whining I hate
so much <grin>.

Hey, maybe I can develop an RSQL syntax... Rational SQL!  <laughing>

Anyway, as always, folks, thanks for the comments.  They keep me honest and
on my toes.

Joe