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

What if you selected all of the items from the receipt history file that
have exactly one record, and then joined that with all of the record
from the item master file that have a receipt date of today?

with itemcount as(
select 
 item_number
from
 DBMSTF.DMRECHST
group by
 item_number
having
 count(*) = 1
)

Select 
 * 

From 

 DBMSTF.DMITMMST a join
 itemcount b on
 a.item_number = b.itemnumber
Where 
 DTRCT = 20060622
Order By 
 WHSLC

And for added fun, you don't even need to get a date and pass it, you
can just use SQL date functions, if we want to run the report for today
we could do.


with itemcount as(
select 
 item_number
from
 DBMSTF.DMRECHST
group by
 item_number
having
 count(*) = 1
)

Select 
 * 

From 

 DBMSTF.DMITMMST a join
 itemcount b on
 a.item_number = b.itemnumber
Where 
 DTRCT=
decimal(year(curdate())*10000+month(curdate())*100+day(curdate()),8)
Order By 
 WHSLC

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Thursday, June 22, 2006 2:00 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL select, join, and count(*)

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.