Re: SQL select, join, and count(*)

   When you use SQL CURSOR you are reading the first of a set of conditions,
   in which you need not loop through them all.  You can link that set of
   conditions to contents of other files that are relevant.

   I have RPG SQL programs with tons of different cursors defined in the same
   program for different conditions, scenarios.

   My cursors are never a single SQL statement.
   You have to open, access, close the cursor.
   I usually feed the data into a data structure, so the definition of the
   cursor is in the opening, and I can then access the cursor different
   places in the program.  You have to be careful to sequence the subroutine
   nameing placement so that you do the open access close in that sequence.

   I have some RPG SQL in which
   1. I do a count(*) which is also an EXIST check, then depending on what
   count(*) and error checking determines
   2. Go after relevant data

   You can do SQL with WHERE some condition inside parentheses.  I usually
   use if for JOIN NO_MATCH type stuff, such as list all inventory
   transactions on master controls that got deleted or no longer exist, or
   list all end items that are lacking a customer, but I guess you could use
   if count(*) is some result.

   We have similar scenario with items set aside for QC to take action, which
   sometimes get overlooked far too long.  Plus, I been thinking about
   creating a report that identifies items that got their cost changed by
   more than a microscopic amount, since we typically find out considerable
   time later that costs need repairing, and in the meantime countless
   reports have been contaminated.

   Depending on how you navigate your transaction history, you might have
   like in our item master file there is a counter of transaction hits which
   connects to a record # in the transaction history file ... record # 1 on
   any item being the *FIRST entry of that item in the transaction history.

   We have logical access to transaction history, such that the *FIRST entry
   for an item is relevant to your interest.

   You can create a JOIN LOGICAL but the keys have all got to be from ONE of
   the files.  If you then run that file as part of the input to an OPNQRYF
   you can key that on fields from several files.

   Depending on how you manage your transaction history, there may be items
   inactive for longer time period than the history is retained.
   When we did our last major BPCS upgrade we lost the ability to keep the
   last *N records on inactive items (a case of some negative enhancements
   mixed in with positive ones)

   While it might make sense for this to run each evening, after everyone
   done for the day, using *CURRENT date, when people working late, and JOBQ
   backed up, you might be better off having GO CMDSCDE launch it at 1 am,
   and have it look for *CURRENT date minus one *DAY.

   Perhaps you could put a trigger on the item master file, to catch when the
   date of last receipt field goes from zero or null contents to valid ISO
   date.

   We also have some change history applications where each evening
   1. Compare current data with prior story, and generate a change record
   when changed.  You gotta be careful in design to allow for deletions,
   otherwise comparing record for record current data vs. old story will only
   catch additions and changes, not deletions.
   2. Replace prior story for the fields we are comparing.

     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.

     --
     Jeff Crosby
     Dilgard Frozen Foods, Inc.
     P.O. Box 13369
     Ft. Wayne, IN 46868-3369
     260-422-7531

     The opinions expressed are my own and not necessarily the opinion of my
     company.  Unless I say so.

     --
     This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
     list
     To post a message email: MIDRANGE-L@xxxxxxxxxxxx
     To subscribe, unsubscribe, or change list options,
     visit: http://lists.midrange.com/mailman/listinfo/midrange-l
     or email: MIDRANGE-L-request@xxxxxxxxxxxx
     Before posting, please take a moment to review the archives
     at http://archive.midrange.com/midrange-l.