I have a question with respect to a query which will work in T-SQL but I cannot get it to work with PL/SQL. Can someone please help.
Select L3D_PARTICIPANT.CASEID, L3D_PARTICIPANT.ID , L3D_PARTICIPANT.LAST_NAME,
L3D_PARTICIPANT.FIRST_NAME , ORIGINAL_COUNTS.COUNTID ,
from lions.l3d_participant, LIONS.L3D2_CR_INST_PARTS_COUNTS ORIGINAL_COUNTS
WHERE L3D_PARTICIPANT.CASEID (+) = ORIGINAL_COUNTS.CASEID
AND L3D_PARTICIPANT.ID (+) = ORIGINAL_COUNTS.PARTID
AND L3D_PARTICIPANT.CASEID = '2000R01439'
AND original_counts.CASEID = (SELECT ORIGINAL_COUNTS.CASEID,
ORIGINAL_COUNTS.PARTID, MAX NEWER_COUNTS.INST_FILING_DATE) AS MAXDATE
FROM LIONS.L3D2_CR_INST_PARTS_COUNTS ORIGINAL_COUNTS,
GROUP BY NEWER_COUNTS.CASEID, NEWER_COUNTS.PARTID
WHERE ORIGINAL_COUNTS.CASEID = NEWER_COUNTS.CASEID
AND ORIGINAL_COUNTS.PARTID = NEWER_COUNTS.PARTID ).
The error message I am getting is that it THERE IS NO PARENTHESIS around the where statement in the nested part of my query.
Your missing a ( for your max function. Its also not good practise to use the same table alias in your main statement and in the subquery. The group by should be after the where clause in your subquery. It also looks like your invalidating the outer join by doing the in clause.