>I have two tables as follows:
>
>
>Table 1: TransmittalLog
>
>Transno Date Iscode
>63773 6/27/03 A
>64228 6/30/03 A
>65117 7/10/03 B
>... ... ...
>
>Table 2: IssueHistory
>
>Drwno Revno Transno
>1 63773
>2 63773
>3 63773
>1 A 64228
>2 A 64228
>3 A 64228
>3 B 65117
>
>
>The results I am looking for are as follows:
>
>Table 3: Results
>
>Drwno Revno Date Iscode
>1 A 6/30/03 A
>2 A 6/30/03 A
>3 B 7/10/03 B
>
>
>The following code in VFP 7.0 produced these results:
>
>SELECT NVL(a.drwno,'') AS drwno,NVL(a.revno,'') AS rev,;
>DTOC(transmittallog.date) AS date ;
>FROM transmittallog ;
>LEFT JOIN issuehistory a ;
>ON transmittallog.transno = a.transno ;
>INTO CURSOR curApprovalIssue WHERE !DELETED() AND ;
>(ALLTRIM(iscode)=='A' OR ALLTRIM(iscode)=='B') ;
>GROUP BY drwno ORDER BY drwno,rev,date
>
>
>However, making the required syntax changes for VFP 9.0 (as follows) does not return the same results:
>
>SELECT NVL(a.drwno,'') AS drwno,NVL(a.revno,'') AS rev,;
>DTOC(transmittallog.date) AS date ;
>FROM transmittallog ;
>LEFT JOIN issuehistory a ;
>ON transmittallog.transno = a.transno ;
>INTO CURSOR curApprovalIssue WHERE !DELETED() AND ;
>(ALLTRIM(iscode)=='A' OR ALLTRIM(iscode)=='B') ;
>GROUP BY drwno,rev,date ORDER BY 1, 2, 3
>
>
>Any suggestions on how to achieve the desired results using VFP 9.0?
>
>Thanks in advance!
Sounds you want something like:
SELECT NVL(a.drwno,'') AS drwno,NVL(a.revno,'') AS rev,;
DTOC(transmittallog.date) AS date ;
FROM transmittallog ;
LEFT JOIN issuehistory a ;
ON transmittallog.transno = a.transno ;
WHERE !DELETED() AND (iscode=='A' OR iscode=='B') ;
AND revno = (select MAX(revno) FROM issuehistory b WHERE a.drwno = b.drwno) ;
ORDER BY drwno,rev,date ;
INTO CURSOR curApprovalIssue
Cetin
