Infor System21 DB2 for i SQL Query Generator

-- Run these on a command line before trusting any field name below
-- IBM i commands:
--   DSPFFD FILE(OSLD1F3/OEP40)
--   DSPFFD FILE(OSLD1F3/OEP55)
--   DSPFFD FILE(OSLSLF3/SLP05)
--   DSPDBR FILE(OSLD1F3/OEP40)
-- System21 commands in OSLSYS (prompt with F4):
--   DSPFLD   display a file's layout
--   DSPKEY   display a file's access path
--   DSPDEP   display all logical files of a physical file

-- Sales order header (OEP40) joined to order lines (OEP55)
SELECT h.*, l.*
  FROM OSLD1F3.OEP40 h
  JOIN OSLD1F3.OEP55 l
    ON l.CONO55 = h.CONO40
   AND l.ORDN55 = h.ORDN40
 WHERE h.CONO40 = '01'
 ORDER BY h.ORDN40
 FETCH FIRST 100 ROWS ONLY;

-- Order headers with the customer record from the Sales Ledger (SLP05)
SELECT h.ORDN40, h.CUSN40, c.*
  FROM OSLD1F3.OEP40 h
  LEFT JOIN OSLSLF3.SLP05 c
    ON c.CONO05 = h.CONO40
   AND c.CUSN05 = h.CUSN40
 WHERE h.CONO40 = '01'
 ORDER BY h.ORDN40
 FETCH FIRST 100 ROWS ONLY;

-- Item stockroom balance profile (INP60) for one item
SELECT *
  FROM OSLD1F3.INP60
 WHERE PNUM60 = 'ITEM-1001'
 FETCH FIRST 100 ROWS ONLY;

-- Inventory item master (INP35)
SELECT *
  FROM OSLD1F3.INP35
 WHERE PNUM35 = 'ITEM-1001';

-- Order lines for the same item (OE references the item as CATN)
SELECT l.*
  FROM OSLD1F3.OEP55 l
 WHERE l.CONO55 = '01'
   AND l.CATN55 = 'ITEM-1001'
 FETCH FIRST 100 ROWS ONLY;

-- Sales orders held by a user or a failed program (ACTF40 not blank)
SELECT ORDN40, ACTF40
  FROM OSLD1F3.OEP40
 WHERE ACTF40 <> ''
 FETCH FIRST 100 ROWS ONLY;

-- Items held in maintenance (ACTF35 not blank)
SELECT PNUM35, ACTF35
  FROM OSLD1F3.INP35
 WHERE ACTF35 <> ''
 FETCH FIRST 100 ROWS ONLY;

-- Most applications also log locks in control files, for example INP99
SELECT *
  FROM OSLD1F3.INP99
 FETCH FIRST 100 ROWS ONLY;