Re: SQL .. samples...

["Richard B Baird" <rbaird@xxxxxxxxxxxxxxxxxxxxx>]

Tim,

here is a reasonably complicated one.  I used QryManager (crtqmqry,
strqmqry).  It was a one-off query I used to populate a file, that a power
user wanted to download to access.  I created the file using DDS, but could
have just as easily done it with sql.

it contained data from 5 different files, joined.  1099 info (payment
activity for 2002).   nothing fancy, but gives you an idea of what can be
done.   next, I will set it up to allow it to be run every year.

You don't need to use qualified fields if each files fields are unique.

if the file identifiers look familiar, it's because I find it easy to put
all of the files i want to join in a qry/400 query, and then cut and paste
the qualified field names from there, rather than typing them in.  I never
intended to make the query runnable, just an intermediate step to make it
faster.

chew on this a little ;)

insert into
AU1OBJ/V99INC
select T01.VENID,     T01.ISSUDATE,  T01.STTSPAYM,
       T01.DATEVOID,  T01.SWVDPOST,
       T03.VCHNBR,    T03.VCHLINE,   T03.AMTPAYM,
       T02.ORIGORG,   T04.INVOICE,   T04.CLSCODE1,
       T04.LINEAMTT,  T04.FRTAMTT,   T04.APLFRTSW,
       T04.AMTLNTOT,  T04.AMTTAX1T,  T04.AMTTAX2T,
       T04.AMTTAX3T,  T04.AMTYAX4T,
       T05.TAXID,     T05.VENADDR,
       T06.VENNAME,   T06.ADDR1,     T06.ADDR2,
       T06.ADDR3,     T06.ADDR4,     T06.CITY,
       T06.STATE,     T06.POSTAL
from   M30DAP/PYM0 T01
join   M30DAP/PVS0 T02
on     T01.ORG      = T02.ORG
and    T01.VENID    = T02.VENID
and    T01.IDRUNPAY = T02.IDRUNPAY
and    T01.CNTLNBR  = T02.CNTLNBR
join   M30DAP/PVL0 T03
on     T01.ORG      = T03.ORG
and    T01.VENID    = T03.VENID
and    T02.VCHNBR   = T03.VCHNBR
and    T01.IDRUNPAY = T03.IDRUNPAY
and    T01.CNTLNBR  = T03.CNTLNBR
join
       M30DAP/VOL0 T04
on     T02.ORIGORG  = T04.ORIGORG
and    T02.VCHNBR   = T04.VCHNBR
and    T03.VCHLINE  = T04.VCHLINE
and    T01.VENID    = T04.VENID
left outer join
       M30DAP/VEN0 T05
on     T02.VENID    = T05.VENID
left outer join
       M30DAP/VNA0 T06
on     T02.VENID    = T06.VENID
and    T05.VENADDR  = T06.VENADDR
where  ( (T01.ISSUDATE between 20020101 and 20021231 and
          (T01.DATEVOID = 0 or
           T01.DATEVOID > 20021231)
         )
or       (T01.DATEVOID between 20020101 and 20021231 and
          T01.ISSUDATE  < 20020101)
       )
and     T01.STTSPAYM  > '0'
and     T02.ORIGORG   IN ('SNS501', 'SNS502')
and     T04.CLSCODE1  IN ('M1', 'M6', 'M7')