>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'"
Steven,
Please next time format your code. It is hard to read and understand (at least for me)
Also why not use TEXT ENDTEXT block when you build such statements. It will be easier for you also:
TEXT TO testString NOSHOW TEXTMERGE PRETEXT 15
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
INNER 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'
ENDTEXT
I have questions here. Is inspectord is datetime?
And if it is datetime what you store there? Only date part of full datetime?
Also what you want as a result for these two situations:
1. The record is first rejected and after that accepted?
2. The record is first accepted and after that rejected?
O, BTW is this a typo here:
mt1.partno = '116405-015' Or mt1.partno = '116405-015'
Both partnos are equal, then why you need OR?
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.