RE: Joining PCP62 and INP95

Joining on those three fields does not get me unique records, because people
make entries for the same part in the same company on the same work order. 

The query I have so far is as follows.

SELECT                                                                  
cono95,
pnum95,
dtmv95,
mqty95,
nshf62
FROM      
  OSLD1F3.INP95 T01,                                          
  OSLPDF3.PCP62 T02                                           
WHERE     
  CONO95 = CONO62  AND                                            
  PNUM95 = COMP62  AND    
  REFN95 = WORD62 AND                                      
  3600*hour(tmmv95)+60*minute(tmmv95)+second(tmmv95) >=
3600*hour(reft62)+60*minute(reft62)+second(reft62)-8  AND
  3600*hour(tmmv95)+60*minute(tmmv95)+second(tmmv95) <=
3600*hour(reft62)+60*minute(reft62)+second(reft62)  AND

  REFD95 = REFD62  AND

  PNUM95 LIKE '* LABOR%' and                             
  mqty95 = -1 * aval62

and it has three problems. If a single work order takes longer then eight
seconds to process, it will not show up. If someone makes 2 issues on the
work order within 8 seconds of each other, and the issues are for the same
amount, I pull duplicate records. Finally if someone makes an entry close
enough to midnight (less then 8 seconds), and the system takes long enough
to process (long enough to take it into the next day), the record will not
show up. 

        Than being said, this is a very lame hack, and if anyone has a
better idea I would love to hear it.

Chris



-----Original Message-----
From: Johnson, Duane [mailto:djohnson@xxxxxxxxxxxxx] 
Sent: Wednesday, August 11, 2004 3:06 PM
To: 'System 21 Users'
Subject: RE: [SYSTEM21] Joining PCP62 and INP95

Why, yes, I may have noticed something to the effect that she may be on
vacation.  From the 9th through the 13th.  Of August.

By the way, you should link on at least three fields.
CONO95 = CONO62
REFN95 = WORD62
PNUM95 = COMP62

That should come close to getting you unique items.

        Duane Johnson
        Information Technology Services
        Programming Manager
        Coleman Powermate Inc.
        4970 Airport Rd.
        Kearney, NE 68847
        djohnson@xxxxxxxxxxxxx

-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]
On Behalf Of Richard_Caldicott@xxxxxxxxxxxxxxx
Sent: Wednesday, August 11, 2004 1:39 PM
To: System 21 Users
Subject: Re: [SYSTEM21] Joining PCP62 and INP95


Does anyone know if Diana Larsson is on vacation?


_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.

_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.