|
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 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.