RE: SQL explained

["Jonathan Mason" <jonathan.mason@xxxxxxxxxxxxxxxx>]

Hi Frank

Here goes, I've reformatted the snips for clarity...

>> (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)

A view is the SQL equivalent to a logical file.  This statement is telling
SQL to create an SQL View called DOUGGIE in library ROB.  The column (field)
names for the view are to be SLSMGR, SLSPSN, CUSTNO, YTDHG, ORDERNO, LINE#
and CYLRNT.

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

This is the subselect clause of the CREATE command and is telling SQL where
to get the values to populate the seven columns defined in (1) above.  Each
of the fields in (2) are qualified by the name of the file, e.g. SLSPSNF,
CUSTMAST and LINEITEM, creating a view that covers multiple files similar to
a Joined-Logical File.

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

This part joins the three files together for the view.  There are a number
of
different ways of joining files together - think of Query where you can have
straight matches, matches with primary or unmatched with primary.

Inner joins only produce results where matches exist in both files.  Left
Outer joins, on the other hand, also shows results where there is no match
in
the file being joined to.

For example, in the sample data Rob provided there are six different values
for
the SLSPSN column in the SLSPSNF table (100, 110, 120, 200, 300 and 700).
By
specifying a LEFT OUTER JOIN between SLSPSNF and CUSTMAST on the SLSPSN
column,
the results will be:

+++ SLSPSNF +++  +++ CUSTMAST +++
SLSMSGR  SLSPSN  CUSTNO     YTDHG
=======  ======  ======  ========
 100      100    12345   10000.95
 100      110    12346     575.23
 100      120    <null>    <null>
 100      200    <null>    <null>
 100      300    <null>    <null>
 700      700    12347     999.47

The first LEFT OUTER JOIN joins tables SLSPSNF to CUSTMAST on the SLSPSN
column
and the result is joined to the LINEITEM table on the CUSTNO.

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

This is an SQL Select statement that Rob is using to show the results of the
VIEW created above.  Here he is instructing SQL to show the SLSMGR, SLSPSN
and
YTDHG columns from the view (logical file) and to also show the largest
customer
number (MAX(CUSTNO)) and the total of the CYLRNT colum (SUM(CYLRNT).

>> (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

The GROUP BY and ORDER BY are subclauses of the SELECT statement in (4)
above.  So,
SQL has been told which columns to retrieve and total, etc, and is now being
told
to sort them (ORDER BY clause) into SLSMGR, SLSPSN and descending YTDHG
sequence,
and to then put level breaks (GROUP BY clause) against the columns so that
the
MAX(CUSTNO) and SUM(CYLRNT) have something to work with.  See the table
beneath
Rob's code for an example of the result.

>> (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))

Four questions in one...

a) A Table is equivalent to a Physical File, a View is equivalent to a
Logical file.

b) INT NOT NULL is defining the columns SLSMGR and SLSPSN as integer types
and saying
   that they cannot contain null values, i.e. they must be populated.

c) The parentheses are used to group all of the attributes for the table
together
   within the CREATE statement.

d) The CONSTRAINT clause is identifying column SLSPSN as the primary key for
the table.
   The constraint is being given the name SLSATH_SLSPSN as an ID which can
be used later
   whenever we want to change the constraint on the table.

>> (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)

Here, the constraints are identifying that the columns SLSPSN and CUSTNO are
foreign keys,
i.e. they are keys to other tables.  By specifying them in a constraint, the
operating
system will automatically ensure that the link between the two tables
retains its integrity
and key values cannot be removed from one table where they are needed by
another.  The
REFERENCES keyword simply identifies that link for the constraint.

Column YTDHG is being defined as a 7.2 decimal number, it is not being
redefined as it does
not already exist.

Phew, there was I thinking "a quick answer".  Hopefully, it'll help a little
bit.

All the best

Jonathan
www.astradyne-uk.com