jimjilbang9.sql

-- Begin lines with two dashes to have the SQL engine ignore them. Great for comments or for storing an old line of code that you might want to use again later.

-- IBM i command STRSQL enters interactive SQL, from which you can run SQL statements.

-- The SOFPACK table stores information about software packages.

-- In SQL, use SELECT to query tables.
-- The * here is a wildcard meaning all columns.
SELECT * FROM sofpack

-- Pick specific fields by referring to them by name instead of using *.
SELECT packno, app FROM sofpack

-- Use 'WHERE' to filter out rows that don't match certain criteria.
-- This filter gives us the package numbers for only packages at 'READY' status.
SELECT packno, app FROM sofpack
WHERE status = 'READY'

-- Multiple WHERE clauses can be combined using AND or OR.
-- Here, the 'NOT IN' clause is used to eliminate packages for the non-FIGARO applications Thenon Adjust Objects, JHC Utilities and Environment Manager.
-- The 'AND' statement is indented for readability only; SQL ignores extra spaces and new lines.
SELECT packno, app FROM sofpack
WHERE status = 'READY'
  AND app NOT IN ('TAO', 'JUT', 'ECM')
  
-- The JOIN clause is used to join together two or more tables, connecting them based on some common field values.
-- This statement gives us every IR and CR in every FIGARO package at 'READY'.
-- The ON condition specifies how the tables are related; here, the packno field is in both tables and we want to relate rows with the same packno.
-- Note that fields appearing in more than one table such as 'app' must be prefixed by the table name, or else they are ambiguous.
SELECT irnum, crnum, sofsrc.app
FROM sofpack
JOIN sofsrc
  ON (sofpack.packno = sofsrc.packno)
WHERE status = 'READY'
  AND sofpack.app NOT IN ('TAO', 'JUT', 'ECM')

-- The DISTINCT clause condenses duplicate rows down to a single instance. It's often a sign of bad design and so we can look at removing this later, but for now it serves the purpose of eliminating duplicates.
SELECT DISTINCT irnum, crnum, sofsrc.app
FROM sofpack
JOIN sofsrc
  ON (sofpack.packno = sofsrc.packno)
WHERE status = 'READY'
  AND sofpack.app NOT IN ('TAO', 'JUT', 'ECM')

-- The WITH clause is used to create Common Table Expressions, which are simply ways to define virtual tables to use later in the code. Here, the common table 'readycrs' is created with WITH and used in the SELECT statement that follows.
-- Common Table Expressions are a powerful way to encapsulate an idea, such as the list of CRs that are ready, and contain all the logic in one place so you can concentrate on other things.
WITH readycrs AS
(
SELECT irnum, crnum, sofsrc.app   
FROM sofpack                                   
JOIN sofsrc                                    
  ON (sofpack.packno = sofsrc.packno)          
WHERE status ='READY'                          
  AND sofpack.app NOT IN ('TAO', 'JUT', 'ECM') 
)

SELECT * FROM readycrs