Re: SQL explained -- MIDRANGE-L

Frank

You got a lot of good answers to this. Let me point you to the manuals to get a better understanding. Everything you asked is answered there pretty well, but a person might not know where to start.

Go to www.iseries.ibm.com/infocenter and click on the V5R3 version. Once there, click on Database, then Printable manuals (IIRC). Look for the SQL Reference. In there, look at the first few parts of "Chapter 1. Concepts". Then look at "Chapter 4. Queries", where all the stuff about ORDER BY and GROUP BY, etc., is to be found.

You won't need much of "Chapter 2. Language elements" to understand things, but you will want to go there eventually. "Chapter 3. Built-in functions" tells you what you can do, but, again, hardly any of this was in your statement. Finally, you want to look at "Chapter 5. Statements", where things like CREATE TABLE and CREATE VIEW are described.

Finally, anything you need to know about in that statement, do an Acrobat search in the Reference manual. You might also want to look at the SQL Programmer's Guide.

If you've used Query/400 and/or OPNQRYF, these things will fall into place pretty quickly, I think.

Also, go to www.iseries.ibm.com/db2 and click on the Support tab (IIRC) and dig around. There are references there to online classes, I think, that can get you going. If not, just do a google on "sql tutorial" - every version is pretty much the same, and anything you learn in MS Access or SQL Server or Oracle tutorials will also do you well on the 400. The usual differences include whether to use a semicolon at the end of a statement. And each dialect will have some functions that only it has - so check against those chapters in the Reference on statements and built-in functions for compatibility.

HTH
Vern

At 04:48 PM 3/29/2005, you wrote:

I'm currently developing an RPGIV report program that uses an "old school"
 work file
method to process a file with millions of records and it's very slow.  If I
 choose
to process 30 days of information, it takes an hour to spit out a report.

Rob Berendt provided the example below.  I believe it shows how to process
 a large
number of records using SQL.  Please correct me if that's not how it was
 meant to
be used.

If anyone can help explain the SQL code below, I'd appreciate it.  I've
 posted
questions before each line that I have questions about.  Please use any
references to RPGIV if you think it might help me understand.  My questions
 are
numbered as (1),(2),(3) etc...

Thanks,

Frank

---------------------------------------------------------------
SLSPSN - Sales person file
 SLSMGR    SLSPSN
 100       100
 100       110
 100       120
 100       200
 100       300
 700       700

CUSTMAST
CUSTNO    SLSPSN     YTDHG
12345     100     10000.95
12346     110       575.23
12347     700       999.47

LINEITEM
CUSTNO    ORDERNO   LINE#  CYLRNT
12345           1       1    1.95
12345           1       2    4.25
---------------------------------------------------------------

(1) What does this SQL section do? Does it create a PF named DOUGGIE in
 library ROB?  Does it already have
to exist?  When the program ends does it automatically get deleted when the
 job ends?  Is the view only accessible to
the current job?
CREATE VIEW ROB/DOUGGIE
 (SLSMGR, SLSPSN, CUSTNO, YTDHG, ORDERNO, LINE#, CYLRNT)

(2) What is this section doing?
AS SELECT
 SLSPSNF.SLSMGR, SLSPSNF.SLSPSN,
 CUSTMAST.CUSTNO, CUSTMAST.YTDHG,
 LINEITEM.ORDERNO, LINEITEM.LINE#, LINEITEM.CYLRNT

(3) What is "LEFT OUTER JOIN" doing?
FROM
 (SLSPSNF
  LEFT OUTER JOIN CUSTMAST USING (SLSPSN))
  LEFT OUTER JOIN LINEITEM USING (CUSTNO)

(4) Please explain this section.
SELECT SLSMGR, SLSPSN, MAX(CUSTNO), YTDHG, SUM(CYLRNT)
FROM ROB/DOUGGIE

(5) It's probably very obvious, but can someone explain in
more detail the functions of GROUP and ORDER?
GROUP BY SLSMGR, SLSPSN, YTDHG
ORDER BY SLSMGR, SLSPSN, YTDHG DESC

SLSMGR          SLSPSN   MAX ( CUSTNO )       YTDHG   SUM ( CYLRNT )
   100             100           12,345   10,000.95             6.20
   100             110           12,346      575.23                 -
   100             120                 -           -                -
   100             200                 -           -                -
   100             300                 -           -                -
   700             700           12,347      999.47                 -

(6) What's the difference between a TABLE and a VIEW?
What's "INT NOT NULL"?  Why is it in parentheses?  What's the "CONTSTRAINT"
section of the code doing?

CREATE TABLE ROB/SLSPSNF (SLSMGR INT NOT NULL, SLSPSN INT NOT NULL,
CONSTRAINT SLSATH_SLSPSN PRIMARY KEY (SLSPSN))

(7) What's the "REFERENCES" section of the code doing?  Is YTDHG being
redefined as 7,2?

CREATE TABLE ROB/CUSTMAST (CUSTNO INT NOT NULL, SLSPSN INT NOT
NULL, YTDHG DEC ( 7, 2), CONSTRAINT CUSTMAST_CUSTNO PRIMARY KEY
(CUSTNO), CONSTRAINT CUSTMAST_SLSPSN FOREIGN KEY (SLSPSN)
REFERENCES ROB/SLSPSNF (SLSPSN) ON DELETE NO ACTION ON UPDATE NO
ACTION)

CREATE TABLE ROB/LINEITEM (CUSTNO INT NOT NULL, ORDERNO INT NOT
NULL, LINE# INT NOT NULL, CYLRNT DEC (7 , 2), CONSTRAINT
LINEITEM_PRIKEY PRIMARY KEY (ORDERNO, LINE#), CONSTRAINT
LINEITEM_CUSTNO FOREIGN KEY (CUSTNO) REFERENCES ROB/CUSTMAST
(CUSTNO) ON DELETE NO ACTION ON UPDATE NO ACTION)

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