Re: AS400 Query Beginner's Instructions

Many of the "cool" reporting tools require a bit of set up. This may
include:
- Field renames,
- Joining the tables together
- Calculating fields, like BPCS on hand
and much more.
I'd argue that if this is the main reason that a "cool" tool appeals to
you the unleash the hounds. Turn your developer loose and tell them to
create some decent views. After all wouldn't this:
create view rob/iimV01 as
select iprod as itemnbr,
iopb+iadj+irct-iiss as OnHand
from iim
where iid='IM'

simplify a query down to
select itemnbr, onhand from iimV01


And for a user to have a simple query like
select * from rob/openorders
order by daysdue

ORDERNO CUSTOMER# CUSTPO# LINE# ITEMNUMBER ORDBALANCE REQUESTDBY
DAYSDUE ITEMDESC
102568 11755 F55469 1 29527244 .000 1999-01-28 4,026-
HARNESS-CAB WIRING
102508 11755 F55429 2 29506596 .000 1999-03-14 3,981-
HARNESS ASSY

All you have to create is a decent view like
create view rob/openorders as
select hord as orderno,
hcust as customer#,
hcpo as custPO#,
lline as line#,
lprod as itemnumber,
lqord-lqshp as ordbalance,
numtodate(lrdte) as requestdBy,
days(numtodate(lrdte)) - days(current date) as daysdue,
idesc as itemdesc,
iclas as itemclass
from (gdidivf/ech left outer join gdidivf/ecl
on ech.hord=ecl.lord) left outer join
gdidivf/iim
on ecl.lprod=iim.iprod
where hid='CH' and lid in('CL', 'CS', 'LI') and iid='IM'

Doesn't that beat the snot out of redoing that logic in every Query?

Rob Berendt