Re: Item with last transaction date

I've used Access and other tools. If you have the same tables, views and
joins in Access that you have in DB2, and still refuse to use RI
constraints and what not like BPCS does then it's not any easier in
Access. I've converted stuff from Access to DB2 on the i. Of course, it
might have been the Access developer there - an old RPG programmer who
wouldn't use RI constraints and stuff in Access any more than he would use
them in DB2.

Most of the "benefits" of alternative tools is because they often
encourage you to define metadata or to consolidate data to ease querying
of the data. Most of this could be resolved by decent joins defined in
DB2. For example, if you often find yourself creating queries which join
IIM, IIC, ECL, ECH, RCM and more then why not create a view that does all
this and just have the users query that? The benefits include:
- Much easier for the users
- You can automatically calculate on hand instead of having to calculate
that in the query
- You can "encourage" them to not to forget about allocated by having an
on hand - allocated column as available
- You can rename column names into something meaningful.
- Automatically handle active record codes
- No duplication of data wasting space
- Live data instead of duplicated data
- Ease date calculations by converting into real dates instead of some
number

One view I whipped up quickly for this email (and forgive me if I, too, am
confused by all the BPCS subtleties) is as follows:
create view rob/OpenOrders as
select
ech.hord as OrderNumber,
numtodate(ech.hedte) as DateOrderEntered,
ech.hcust as CustomerNumber,
ech.hdtot as OrderTotal,
ech.hsal as Salespersonnumber,
ech.hcpo as customerPOnumber,
ifnull(ecl.lline,0) as orderline,
ifnull(ecl.lprod,'null') as orderItemnumber,
ifnull(ecl.lqord,0) as qtyOrdered,
ifnull(ecl.lqshp,0) as qtyShipped,
ifnull(ecl.lqord,0) - ifnull(ecl.lqshp,0) as qtyRemaining,
numtodate(ecl.lsdte) as ScheduledShipDate,
case
when ecl.lstat is null then 'null'
when ecl.lstat='E' then 'OPEN'
when ecl.lstat='I' then 'SOMETHING'
when ecl.lstat='P' then 'PICKED'
end as LineStatus,
ifnull(iim.idesc,'blank') as ItemDescription,
case
when iim.iopb is null then 0
else iim.iopb + iim.iadj + iim.irct - iim.iiss
end as OnHand,
case
when iim.iopb is null then 0
else iim.iopb + iim.iadj + iim.irct - iim.iiss - iim.icusa
- iim.iprda
end as ItemQtyAvail
from ech left outer join ecl on ech.hord = ecl.lord
left outer join iim on ecl.lprod = iim.iprod

Now you can simply query:
select * from openorders

The view can be extended to:
- Add sales person name
- select only active records from each file
- since it is called "openorders" select only open orders

Now you can also easily do
select dateorderentered, ordernumber, customernumber
from openorders
where current date - dateorderentered > 10000
This will give you orders entered over a year ago. That number is yymmdd
as in
20101021 - 20021001 = 8 years, 0 months and 20 days or 80020
or you could also do
select dateorderentered, ordernumber, customernumber
from openorders
where dateorderentered > current date - 100 days
to only get orders entered within the last 100 days.

You could also create matching shorter field names to ease legacy tools
like Query/400. For example
create view rob/OpenOrders as
select
ech.hord as OrderNumber for column ordernum,
numtodate(ech.hedte) as DateOrderEntered for column DtOrdEnt,
...

There's also nothing stopping you from using that view in imbedded sql
either.

When creating a view I recommend always using the table names and not to
try using logical file names in your FROM clause.

Rob Berendt