Derived Column in Query

[AjarnMark]

Can somebody help me with this syntax? I am taking two working queries and putting them together such that one becomes a derived column in the other. I am guessing that I must just be missing some small syntax point (I don't use Oracle much) because this query runs fine in a SQL Server. I thought I had gotten all the syntax converted, but I'm getting an error: "ORA-00936: Missing Expression". Here's the statement:

SELECT A.CNTRCT_ID, A.VENDOR_ID,
(Select X.XLATSHORTNAME
From Sysadm.XLATTABLE X
WHERE X.EFFDT =
( SELECT MAX(EFFDT)
FROM Sysadm.XLATTABLE
WHERE X.FIELDNAME = FIELDNAME AND X.LANGUAGE_CD = LANGUAGE_CD AND X.FIELDVALUE = FIELDVALUE
AND EFFDT <= TO_DATE('12/26/2001','MM-DD-YY')
)
And X.FIELDNAME = 'CNTRCT_STATUS' And X.FIELDVALUE= A.CNTRCT_STATUS
) as StatusExtended,
A.CNTRCT_BEGIN_DT, A.CNTRCT_EXPIRE_DT, A.DESCR, B.NAME1
FROM Sysadm.PS_CNTRCT_HDR A, Sysadm.PS_VENDOR B
WHERE A.Z_PURCH_DOC_TYPE = 'B' AND A.CNTRCT_STATUS <> 'C' AND B.VENDOR_ID = A.VENDOR_ID AND B.SETID = A.VENDOR_SETID
AND A.CNTRCT_EXPIRE_DT BETWEEN TO_DATE('12/26/2001','MM/DD/YY') AND TO_DATE('1/25/2002','MM/DD/YY')
Order By B.NAME1

Thanks for your help!

[aph]

Remove the , from the 1st line A.VENDOR_ID

[AjarnMark]

Thank you for your reply, but I did this and it does not solve my problem. The SELECT statement inside the parentheses should generate a single value for each row in the main SELECT, thereby acting as a derived column.

More help, please.