>>>>The following SPT string was suggested a while back and has worked fine until today. Today it hit a part number taht was inspected twice on the same day in which one record shows rejected the other accepted. In this case I only need the accepted returned. What do I need to do to make this happen?
>>>>
>>>>testString = "Select recseq, CASE WHEN faistatus = 1" +;
>>>>" THEN 'Accepted' WHEN faistatus = 2 THEN 'Rejected' ELSE 'SEE ORIGINAL FAI' END" +;
>>>>" As fais, inspectord, dwgrev From webprddt6.drawmfai mt1" +;
>>>>" JOIN ( SELECT webprddt6.drawmfai.partno, MAX(webprddt6.drawmfai.inspectord) AS MaxDate FROM webprddt6.drawmfai GROUP BY webprddt6.drawmfai.partno, webprddt6.drawmfai.vendorcode ) mt3" +;
>>>>" ON mt3.partno = mt1.partno AND mt3.MaxDate = mt1.inspectord" +;
>>>>" Where (mt1.partno = '116405-015' Or mt1.partno = '116405-015') And Left(mt1.vendorcode,5) = 'G7135'"
>>>
>>>
>>>
>>>and whatever = 'Accepted'
>>
>>Nope,
>>That will filter ALL records and you get only Accepted records as a result.
>
>okay then.. and whatever != 'rejected'
:-)
If I understood Steven correctly he wants ALL records no matter if they are rejected or accepted or whatever they are, just when he have double records for one day one accepted and one rejected he wants only accepted one.
But maybe I'm wrong and you are right.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.