Computer cheat sheet

SQL statements

To get a summary count:
runsql 'select pfpo, pftrid, count(*) from t37files/vpoacr1 group by pfpo, pftrid order by pfpo, pftrid'

To summarize and sort records:
runsql 'select usitem, usrund, sum(usweight) from qtemp/vincase group by usitem, usrund order by 3 desc'

Using between:
runsql 'select count(*) from qtemp/mvgldet99 where gppost between 200401 and 200412'

To update source member record dates:
OVRDBF FILE(QDDSSRC) TOFILE(MARKLIB/QDDSSRC) MBR(GLINAGFM)
runsql 'update qddssrc set srcdat=070702 where srcdat > 070611 and srcdat < 070730'

To increment a field:
runsql 'update marklib/mvinitmty set labeldate = labeldate + 1 day'

To select records from two files using a substring:
runsql 'select icitem from vinitem a join caselabl b on substr(a.icitem,1,7)=b.itmbas where b.csltxt like ''@%'''

To select counts greater than 1 (in Access)
     SELECT [FIELDS NOT SAME LENGTH].FILE, [FIELDS NOT SAME LENGTH].FIELD, Count(*) AS Expr1
     FROM [FIELDS NOT SAME LENGTH]
     GROUP BY [FIELDS NOT SAME LENGTH].FILE, [FIELDS NOT SAME LENGTH].FIELD
     HAVING (((Count(*))>1));

To select parts not in a view over 3 files - each file should have one instance of the part
     select partno, count(partno)
     from viewprtc
     group by partno
     having count(partno)<>3

Using true date fields
runsql 'select * from marklib/mvinitmty where labeldate > ''2008-08-08'''

Select the Item-Balance across all warehouses for item 12345:
select sum(ItemBalance) from Item-Warehouse-File where ItemNumber = 12345

Select the OrderValue for a given order:
select sum(LineValue) from OrderDetail where OrderID = 12345

Select the average order value for salesrep 12345, where the salesrep is stored on the customer:
     select avg(OrderValue) from OrderHeader OH
         join Customer C
             on OH.CustomerID = C.CustomerID
             and C.SalesRep = 12345

Select the total order value, by customer, for salesrep 12345, largest customer first, provided the customer has placed at least 5 orders:
     select CustomerID, sum(OrderValue) as TotalValue, count(*) from OrderHeader OH
         join Customer C
             on OH.CustomerID = C.CustomerID
             and C.SalesRep = 12345
     group by CustomerID
     having count(*) >= 5
     order by TotalValue desc

Select the average order value for orders that are ordered by "OEM" customers, where the order contains any item that is in the item class of "Drill":
     select avg(OrderValue) from OrderHeader OH
         join OrderDetail OD
             on OD.OrderID = OH.OrderID
         join Customer C
             on OH.CustomerID = C.CustomerID
             and C.CustomerType = "OEM"
         join Item I
             on OD.ItemNumber = I.ItemNumber
             and I.ItemClass = "Drill"

Manually performing the cross-tab function using the CASE statement:
Given:

Period	Category	Sales
2002-01	Snacks	3000.25
2002-01	Beverages	2145.01
2002-01	Fruit	175.20
2002-02	Snacks	2167.40
2002-02	Beverages	2022.39

     Select Period,
         Sum(Case When Category='Beverages'
             Then SalesAmt End) As Beverages,
         Sum(Case When Category='Fruit'
             Then SalesAmt End) As Fruit,
         Sum(Case When Category='Snacks'
             Then SalesAmt End) As Snacks
         From Sales
     Group By Period
     Order By Period

Gives you:

Period	Beverages	Fruit	Snacks
2002-01	2145.01	175.20	3000.25
2002-02	2022.39	--	2167.40

Setting a value based on a case
     update myTable
       set field1 = case
         when field2 = 'C' then 'CLT' else 'AGT' end

Retrieving fields from two files:
     Select A.C1USER, B.VENDOR, B.VENDNAME
     From EDIPFCONT1 A Join VENDORFL00 B On A.C1VEND = B.VENDOR
     Where A.C1VNDEDI = ' + @Tic + @Yes + @Tic + ')'
     Order By VENDNAME

Count the number of distinct values of a field in a file
     SELECT count(distinct location), sum(onhand)
     FROM inventory
     WHERE item = 'someitem#'

Get the number of records (based on a prefix):
     select item_number, count(distinct substr(site_id,1,1))
     from myFile
     group by item_number

Using a concatenated field
select concat(substr(itemnbr,1,7),class),itemnbr,class
from r37modsdta/mlblcls

Joining two files using concatenated fields - all records in both files
select * from r37modsdta/mlblcls a join r37modsdta/vinitem b
on concat(substr(a.itemnbr,1,7),a.class)=b.icitem

Joining two files using concatenated fields - only records in first file and not in second file
select itemnbr,class from r37modsdta/mlblcls
where concat(substr(itemnbr,1,7),class) not in(select icitem from r37modsdta/vinitem)

Updating a field in one file based on a value in another file
update t37modsdta/vinitem set icwght = 0
where icitem in(select ifitem from t37modsdta/vinitmb where ifcomp = 22)

Handling null value errors with coalesce (when tqty does not have a value):
     exec sql
     select coalesce(sum(qty),0) into :issues
     from bakbp813/ith
     where tprod=:iprod
     and ttype in ('CI','I')
     and ttdte between :datlo and :dathi
     and twhs between '21' and '69';

How do I create two fields from the value of another field?
  My data, showing the amount field, is:
     record 1:  100.00
     record 2: -100.00
  I want to see:
                     DEBIT  CREDIT
     record 1:  100.00          0.00
     record 2:      0.00     -100.00

  UPDATE MYFILE SET
    DEBIT = MAX(AMOUNT,0),
    CREDIT = MIN(AMOUNT,0)
Which leaves the amount field intact, and gives two new fields which have either zero or the appropriate amount (plus or minus)

The above is for updating the file for later use. For a query, the same thing works, though:
  SELECT MAX(AMOUNT, 0) AS DEBIT, MIN(AMOUNT,0) AS CREDIT

MAX() and MIN() works on system i but not on mysql. This will work on both:
  SELECT case when AMOUNT < 0 then AMOUNT else 0 end as credit,
    case when AMOUNT >= 0 then AMOUNT else 0 end as debit,
    AMOUNT
  FROM GeneralLedger

How to get number of records (based on a prefix):
  Item                     Site
  Number               ID
  NHZF500-051    A1P
  NHZF500-051    E1P
  NHZF500-051    J1D
  NHZF500-051    J1E
  NHZF500-051    J1P
After the execution of the SQL, I want to have the following results:
  NHZF500-051    3

With Prefix as (select distinct itnbr,substr(stid,1,1) from IDFILE) select itnbr,count(*) from prefix order by itnbr

WITH T1 AS (
  SELECT DISTINCT ITEMNUMBER, SUBSTR(SITEID,1,1) AS SUBGROUP
  FROM ROB/PV)
  SELECT ITEMNUMBER, COUNT(*) AS THECOUNT
  FROM T1
  GROUP BY ITEMNUMBER

Gives:
  ITEMNUMBER    THECOUNT
  NHZF500-051      3

I have two files. FileA has id and name, FileB has id and somenum. I want to retrieve id, name and somenum, where somenum is the highest value of somenum for that id.
     select filea.id, filea.name, max(fileb.somenum)
     from filea join fileb on filea.id=fileb.id
     group by filea.id, filea.name

Joining files and keeping records in "file" order
     SELECT 'A' as File, rrn(FILE123A) as recNbr, FILE123A.*
     FROM LIBRARYA.FILE123 FILE123A
     UNION ALL
     SELECT 'B' as File, rrn(FILE123B) as recNbr, FILE123B.*
     FROM LIBRARYB.FILE123 FILE123B
     ORDER BY 1,2

Table has 3 columns - Name, year, count - and I need a total of the "count" field for each name over a 3 yr period, if there are NOT 3 years available then no output row.
     select a.name, a.year, sum(a.amt + b.amt + c.amt)
     from myfile a
          inner join myfile b on a.name = b.name and a.year = (b.year + 1)
          inner join myfile c on a.name = c.name and a.year = (c.year + 2)
     group by a.name, a.year
Performance is not going to be great. RPG records level access or an SQL cursor may be a better solution.

Then...
     select a.name, a.year, a.amt + b.amt + c.amt as TotalAmt
     from myfile a
          inner join myfile b on a.name = b.name and a.year = (b.year + 1)
          inner join myfile c on a.name = c.name and a.year = (c.year + 2)
No need for the sum function...

My original would have 1990, 1991, 1993 --> 1993
He wanted 1990,1991,1993 --> 1990
     select a.name, a.year, a.amt + b.amt + c.amt as TotalAmt
     from myfile a
          inner join myfile b on a.name = b.name and b.year = (a.year + 1)
          inner join myfile c on a.name = c.name and c.year = (a.year + 2)

Return to the top of this page

Debugging

Break points:
     To set a break point when a certain value is reached in debug
          BREAK {line number} when {field} = {value}
          BREAK 74 when rcinv = 13131
          BREAK 759 when gl_gppost = 20071201

Return to the top of this page

FTP

FTP-ing files:
     ftp Qmaplesvr
     bin
     put C:\lstffd\lstffd marklib/plank.savf
     put C:\scnmsgf\scnmsgf marklib/plank.savf
     quit

Return to the top of this page

RPG

Calling RPG with numeric data from command line:
CALL PGM(FREDLIB/MMOCRMNO) PARM(X'01f'' 'Y')

Return to the top of this page

Backups

Backing up a Series i
Are You Saving The Right Stuff? Wiki article

Return to the top of this page