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 get a summary count with distinct records, so
   Field1  Field2
    1            1
    1            1
    1            2
    1            3
    1            4
    1            4
The first count (non distinct) would be 6, the distinct count would be 4
     runsql 'Select count(distinct(field2)) Into :Reccount' or
     runsql 'SELECT COUNT(field1), COUNT(Distinct field2)'
From posts on the 9/30/10 Midrange-L list.

To update a character field with a summary count - right justified and with leading zeros:
runsql 'select right(digits(count(*)),8) from mylib/myfile' or
runsql 'Select DIGITS(DECIMAL(mycount,8,0)) From ...'
From posts on the 9/9/10 Midrange-L list.

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 counts VINBINI records with the same pallet number in multiple bins
     runsql 'select bapalet, count(*) as "pallet count"
     from r37modsdta/vinbini
     where bacmp = 1 and bapalet <> 0 and bapalet <> 999999999999
     group by bapalet
     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)

Add a record to a file based on a record in another file
     runsql 'insert into marktest/caseexp select * from r37modsdta/vincase where uscmp=1 and usorder=383668'
or
     runsql 'insert into marktest/caseexp select * from r37modsdta/vincase where uscmp=1 and usorder=404618 and usserl in
     (''0351019014403'',''0351019315370'',''0351019315371'',''0351019315378'',''0351019315379'',''0351019315380'',
     ''0351019315385'',''0351019315391'',''0351019315392'',''0351019315395'',''0351019315400'',''0351019315401'',
     ''0351019315406'',''0351020719129'',''0351020719130'',''0351021120851'',''0351021120853'',''0351021120874'',
     ''0351021120875'',''0351021120877'')'

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)

Updating a file based on various conditions. If the flag isn't set that field is updated with the same value:
     update contract_file
       Set emp = case when empflag = 'Y'
                 then newUser
                 else emp
             end,
         contact1 = case when contflag1 = 'Y'
                 then newUser
                 else contact1
             end,
         contact2 = case when contflag2 = 'Y'
                 then newUser
                 else contact2
             end
From a post on the 8/18/10 Midrange-L list.

I want to generate results like the following from a table with those columns, however, the summary row does not exist. And, of the detail, I only want the 10 largest rows per library:
   Library      Object      Size
   BIGLIB                     456789
   BIGLIB    BIGOBJ   200000
   BIGLIB    MEDOBJ  100000
   BIGLIB    SMLOBJ    50000
   ...
   MEDLIB                     322457
   MEDLIB   BIGOBJ   150492
   MEDLIB   MEDOBJ   54329
   MEDLIB   SMLOBJ   12222
   ...
   SMLLIB                     105298
   SMLLIB   BIGOBJ    102201
   SMLLIB   MEDOBJ         49
   SMLLIB   SMLOBJ           2

       create table rob.libsum as (
         with totrows as
         (select diobli as library, sum(diobsz) as libsize,
                  Dense_Rank() Over(Order By sum(diobsz) Desc) as libRank
       from qusrsys.qaezdisk
       where diobli<>''
       group by diobli),
       topten as (Select diobli as objLibrary, diobnm as Object, diobsz as Size,
                  Dense_Rank() Over(Partition By diobli Order By diobsz
       Desc) as objRank
       From qusrsys.qaezdisk
       where diobli<>''),
       combine as
         (select library, libsize, librank, object, size, ObjRank
       from Totrows join topten on library=objlibrary)
       select library, '' as object, libsize as size, librank,0 as objrank
         from totrows
       union all
       select '' as library,object,size, librank,objrank
         from combine
       where objrank<=25
       order by librank, objrank)
       with data;
From a post on the 9/20/10 Midrange-L list.

I have a table like so:
   Client Product
       C1      P1
       C1      P2
       C2      P1
       C3      P4
       C4      P1
       C4      P2
       C4      P3
With the max, min and avg functions I can see that on average a client has 2 products, that no client has more than 7 products and none with less than 1. But how can I output Number of products and Number of clients?

     WITH ProductCounts as (SELECT client,
               count(distinct product) as productCount
          FROM qtemp/david
          GROUP BY client
          )
     SELECT productCount, count(*) as clientCount FROM
     ProductCounts group by productCount

Giving:
   NB_PROD   NB_CLIENT
          1                  731
          2                  407
          3                  360
          4                  113
          5                    37
          6                     9
          7                     2
From a post on the 9/2/10 Midrange-L list.

Using Renamed Fields:
With tmpfile as (
SELECT ech.hord,
numtodate(polog.poendt) as day_entered,
numtodate(ech.hedte) as day_ordered
FROM xyzlib/pologpf AS polog
JOIN xyzlib/ech AS ech
ON polog.pokord = ech.hord
)
Select count(*) from tmpfile
where days(day_entered) + 30 < days(day_ordered)

That's one way. Another (mentioned already, not by me) is to use CTE:
WITH T1 as
(
SELECT
numtodate(polog.poendt) as day_entered,
numtodate(ech.hedte) as day_ordered
FROM xyzlib/pologpf AS polog
LEFT JOIN xyzlib/ech AS ech
ON polog.pokord = ech.hord
)
SELECT * from T1
where day_entered+3 > day_ordered

Instead of converting a date into the number of days since 0001-01-01, you can add 30 Days directly.

With tmpfile as (
SELECT ech.hord,
numtodate(polog.poendt) as day_entered,
numtodate(ech.hedte) as day_ordered
FROM xyzlib/pologpf AS polog
JOIN xyzlib/ech AS ech
ON polog.pokord = ech.hord
)
Select count(*) from tmpfile
where day_entered + 30 Days < day_ordered

Even with a index on hord, this requires a full table scan...
WHERE numtodate(hord) + 30 days< CURRENT_DATE

However, this can use an index on hord
WHERE hord< datetonum(CURRENT_DATE - 30 days))

From posts on the 11/08/10 Midrange-L list.

Is there something that can be done in SQL, that would effectively make a bunch of instances of the same file, in several different libraries, look like a single file?
     Yes, UNION ALL clause. For example:
     CREATE VIEW QTEMP/ALLFILES AS
     (SELECT * FROM lib1/FILEA
     UNION ALL
     SELECT * FROM lib2/FILEA
     UNION ALL
     SELECT * FROM lib3/FILEA)
Now query the QTEMP/ALLFILES view or read it sequentially using RLA.

Or,
Say you have LIB1/MYFILE and LIB2/MYFILE. Then you have options:
   If you have something in each instance that identifies it, or you don't care about that -
     SELECT * FROM LIB1/MYFILE
     UNION
     SELECT * FROM LIB2/MYFILE
   If you need to distinguish each library's data -
     SELECT 'LIB1', MYFILE.* FROM LIB1/MYFILE
     UNION
     SELECT 'LIB2', MYFILE.* FROM LIB2/MYFILE
From posts on the 09/15/09 Midrange-L list.

I have a table where my data would be like
   Company Warehouse Item Vendor
        1               1              256          25
        2               1              256          *blanks
        3               1              256          24
        4               1              256          24
        1               1              303          25
        2               1              303          25
What I am looking to do is generate a list which will show all of the rows for items with different vendors (all 256 and no 303 in this case)
     SELECT T1 as (SELECT COMPANY, WAREHOUSE, ITEM, VENDOR FROM MYTABLE)
     , T2 as (SELECT ITEM, VENDOR from T1 GROUP BY ITEM, VENDOR)
   , T3 as (SELECT ITEM, COUNT(*) AS NBRVENDS FROM T2
             GROUP BY ITEM
             HAVING COUNT(*) > 1)
     SELECT * FROM T1 a
     JOIN T3 b on a.ITEM = b.ITEM

or
     SELECT * FROM MYFILE A
     WHERE EXISTS(
      SELECT count(distinct vendor) FROM MYFILE B
      WHERE A.ITEM = B.ITEM
      HAVING COUNT(distinct vendor) > 1 )

or
     with itemVendor as (select distinct item, vendor from mytbl)
      , dupItems as (select item, count(*) from itemVendor
             group by item
             having count(*) > 1)
     select * from mytbl
     where item in (select item from dupItems)

or alternate that might perform better
     select * from mytbl A
     where exists (select from mytbl B
             where a.item = b.item and a.vendor <> b.vendor)
"My first solution is easier to understand, I'd go with that unless performance of it is an issue."
From posts on the 06/14/10 RPG list.

I am trying to run an SQL statement that creates a temporary table over which I want to run some selection criteria. Ideally I would like to add a "WHERE" clause to the select to only show entries where the COUNT(*) is more than a given amount, say 10000. However, I can't find any way to reference the generated column as I don't know the internal name assigned to the column.
Unlike a WHERE clause which is checked for each row read, the HAVING check occurs for each row in the result set after the aggregation. So for instance this statement
      with t1 as ( select rpan8, count(*) as theCount
       from f0311
       group by rpan8
       order by rpan8
       )
      select * from t1
      where theCount > 10000
is the same as
     select rpan8, count(*) as theCount
     from f0311
     group by rpan8
     having count(*) > 10000
     order by rpan8
From posts on the 08/18/09 Midrange-L list.

This is what I need to make sure is true.
     Table 1     Table 2
     02 1.5      02 1.5
     04 16.0    04 16.0
The problem is when
     Table 1     Table 2
     02 1.5      02 1.5
                      04 16.0
or when
     Table 1     Table 2
     02 1.5      02 1.5
     04 16.0
or more so when
     Table 1     Table 2
     02 1.5
     04 16.0

To return all rows that are different:
( Select * from Table1
Except
Select * from Table2)
Union
( Select * from Table2
Except
Select * from Table1)
If all rows in both tables are identical zero rows are returned.
From posts on the 03/10/11 RPG list.

To find where the logical and physical files reside in different libraries:
     SELECT dbflib , dbffil , dbfldp , dbffdp FROM QSYS/QADBLDEP
     WHERE dbflib<>dbfldp AND NOT Substr(dbflib,1,1)='Q'

DSPDBR of all physical files to an outfile, then look for instances where the logical file library is not the same as the physical file library
     1) DSPDBR FILE(LIBRARY/PFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/QADSPDBR)
     2) strsql and run the following query:
     Select whrfi, whrli, whrefi, whreli from qtemp/qadspdbr where whrli <> whreli

     SELECT DBFFIL as MainFile, DBFLIB as MainLib,
       DBFFDP as DepFile, DBFLDP as DepLib,
       DBFTDP as DepType
     FROM QADBFDEP
     WHERE DBFLIB <> DBFLDP
Please be aware that the file name columns are Varchar(128), so maybe you would prefer to CAST or SUBSTR them...

Selecting fields from multiple files, creating new columns, using case
     SELECT A.FKCMP, A.FKYEAR, A.FKNUM, A.FKDEL, B.FMNAME,
          MAX(CASE A.FKLOC WHEN 'DVIN' THEN A.FKFARMNO END) DVFARMNO,
          MAX(CASE A.FKLOC WHEN 'DVIN' THEN A.FKBARNID END) DVBARNID,
          MAX(CASE A.FKLOC WHEN 'BRIN' THEN A.FKFARMNO END) BRFARMNO,
          MAX(CASE A.FKLOC WHEN 'BRIN' THEN A.FKBARNID END) BRBARNID
               FROM MLPFLOCK A JOIN MLPFARM B ON
                    A.FKFARMNO=B.FMFARMNO and a.fkloc = B.FMLOC and
                    A.FKFARMNO=B.FMFARMNO
                         GROUP BY A.FKCMP, A.FKYEAR, A.FKNUM, A.FKDEL, B.FMNAME
                         ORDER BY A.FKCMP, A.FKYEAR, A.FKNUM, A.FKDEL, B.FMNAME

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