× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



All,

I've written 1 RPG SQL program in the past and have come upon a report
project that I think would benefit from SQL, but I need help.

What I want is, at the end of the day, print a report for the warehouse of
every item that was received for the very first time that day.  (The report
is for the warehouse to examine the item for HazMat.  Yes, they could do it
when physically receiving the product, but some are getting missed.  So
we're going to make it a separate task.)

The item master file (DMITMMST) has a date of last receipt field (DTRCT)
defined as 8,0 (I did some Y2K stuff before date fields existed).  The
receipt history file (DMRECHST) has 1 record for each receipt transaction
and the receipt date in there is an *ISO date field.

I could do this report by using this select:

Select * From DBMSTF.DMITMMST Where DTRCT = 20060622 Order By WHSLC

Then, in my RPG program, SETLL/READE the receipt history for each of these
items, count how many receipt records there are, and if there's only 1,
print the item.  But I think I should be able to do the entire selection
within an SQL statement.  I know that a count(*) is probably involved in
there somewhere.  I searched the archives and found a lot with count(*), but
nothing seemed comparable to what I'm trying.  SQL scripting in iNav can
really point out very quickly that you're in over your head. <g>

Joining will be by item number which is ITNBR in the item master and RHITEM
in receipt history.  

I also thought about the initial select against receipt history (since the
receipt date is also in there), but the sort field I want is in the item
master.  And the item master has _far_ fewer records, so I thought the
initial select against it would perform better.

Is this doable in 1 SQL statement?

Thanks.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.