Simpler Sql

run two queries

select serlnmbr,locncode from iv00200

select iv30400.serltnum, iv30300.trnstloc, iv30300.docdate
from iv30400
      join iv30300 on (iv30400.ivdoctyp = iv30300.doctype and iv30400.docnumbr = iv30300.docnumbr)
      join (select serlnmbr,locncode from iv00200) rs
where iv30400.serltnum = rs.serlnmbr   --  FROM RECORDSET ABOVE
and iv30300.trnstloc = rs.locncode          --  FROM RECORDSET ABOVE
and iv30300.docdate <  '5/6/11'            -- More than 7 days, I can do this, a dateadd -7, etc...

now, you have two resultsets... loop through 1 and filter the other one in memory using RowFilter or using select methods on datatable/dataview

or create relation from resultset1 to resultset2 so filtering will be done for you automatically...

or another method: join two queries into one table and work on that, but it may be difficult for you... you may need to select distinct values, and do the same filtering bla bla bla...

@MrJoltCola

Thx for the query.  There was one small part missing but I added it back in.  I'll post it in case anyone looks at this.

MIssing:                 and locncode like '%-%t'

select iv30400.serltnum, iv30300.trnstloc, iv30300.docdate
from iv30400
      join iv30300 on (iv30400.ivdoctyp = iv30300.doctype and iv30400.docnumbr = iv30300.docnumbr)
      join iv00200 on ( iv30400.serltnum = iv00200.serlnmbr and  iv30300.trnstloc = iv00200.locncode and locncode like '%-%t')
where iv30300.docdate <  '5/6/11'
order by 3  

Welcome. In general a join is just exactly what you first implemented in code, a "foreach"

foreach(query1)
   query2

When you write a join (or even a correlated subquery using IN or EXISTS) the database may choose to execute it as a nested loop (foreach) or some sort of merge (hash merge, sort merge), but in any case, letting the database do it is at least an order of magnitude faster than doing it in code, because of all the latency with each parsed SQL statement.

As a passing observation, using the numeric sequence number for the column in your ORDER BY clause is a really bad habit.  If someone doesn't happen to notice that you've udone that and adds in a line, you could really get some screwy results. ;-)

For instance, your code is:

SELECT iv30400.serltnum
  ,iv30300.trnstloc
  ,iv30300.docdate
FROM iv30400
JOIN iv30300
ON(
        iv30400.ivdoctyp = iv30300.doctype
        AND iv30400.docnumbr = iv30300.docnumbr
    )
JOIN iv00200
ON(
        iv30400.serltnum = iv00200.serlnmbr
        AND iv30300.trnstloc = iv00200.locncode
        AND locncode LIKE '%-%t'
    )
WHERE iv30300.docdate < '5/6/11'
ORDER BY 3

Select allOpen in new window

But someone says, "Hey, I want the DocDate as the first column."  So, Joe JuniorProgrammer comes along and changes the query so that it looks like:

SELECT iv30300.docdate
  ,iv30400.serltnum
  ,iv30300.trnstloc
FROM iv30400
JOIN iv30300
ON(
        iv30400.ivdoctyp = iv30300.doctype
        AND iv30400.docnumbr = iv30300.docnumbr
    )
JOIN iv00200
ON(
        iv30400.serltnum = iv00200.serlnmbr
        AND iv30300.trnstloc = iv00200.locncode
        AND locncode LIKE '%-%t'
    )
WHERE iv30300.docdate < '5/6/11'
ORDER BY 3

Select allOpen in new window

Now what happens? ;-)

Or Joe inserts another column:

SELECT iv30400.serltnum
  ,iv30300.trnstloc
  ,iv30300.doctype
  ,iv30300.docdate
FROM iv30400
JOIN iv30300
ON(
        iv30400.ivdoctyp = iv30300.doctype
        AND iv30400.docnumbr = iv30300.docnumbr
    )
JOIN iv00200
ON(
        iv30400.serltnum = iv00200.serlnmbr
        AND iv30300.trnstloc = iv00200.locncode
        AND locncode LIKE '%-%t'
    )
WHERE iv30300.docdate < '5/6/11'
ORDER BY 3

Select allOpen in new window

This solution was correct but make sure that you see my last comment if you are looking at this code .