ORA 00923 - From Keyword not found where expected 1

[IngDep]

Continue to receive the ORA 00923 error upon trying to run the following sql script using Oracle 8.

Any ideas as to a solution?

What am I missing here?

SELECT
CLM.GRP_CD,
CLM.CLCL_ID,
SUM(CL.CDML_CHG_AMT),
SUM(CL.CDML_PAID_AMT),
MAX(ST.Review_Date) Review_DATE,
M.MEB_CK,
M.MEB_NAME,
P.CONTR_ID,
P.CONTR_NAME
FROM EIST.CMC_CDML_CL_LINE CL
INNER JOIN EIST.CMC_CLCL_CLAIM CLM
ON CLM.CLCL_ID = CL.CLCL_ID
INNER JOIN EIST.CMC_MEB_MEMBER M
ON CLM.MEB_CK = M.MEB_CK
INNER JOIN EIST.CMC_CONTR_PROV P
ON CLM.CONTR_ID = P.CONTR_ID
INNER JOIN
(SELECT HP.CLCL_ID
, CLM.CLCL_LAST_REV_DTM
FROM CMC_CLHP_HOSP HP
INNER JOIN CMC_CLCL_CLAIM CLM
ON CLM.CLCL_ID = HP.CLCL_ID
AND CLM.GRP_CD = 8
AND HP.CLHP_ADM_DT >= '08/15/2008') CM
ON CL.CLCL_ID = CM.CLCL_ID

GROUP BY
S.CLCL_ID
, S.CLST_SEQ_NO
, S.CLST_STS_DTM) ST
ON ST.CLCL_ID = CLM.CLCL_ID
AND ST.CLST_SEQ_NO = CLM.CLST_SEQ_NO

GROUP BY
CLM.GRP_CD,
CLM.CLCL_ID,
M.MEB_CK,
M.MEB_NAME,
P.CONTR_ID,
P.CONTR_NAME

HAVING
SUM(CL.CDML_PAID_AMT) >= 100000

 

[Turkbear]

Hi,
Does 8i support that Join syntax?
No - see this article about it:

http://dbaktiar.blogspot.com/2007/07/no-inner-join-with-oracle-8i.html

Try using the (+) syntax that is supported...


The use of Inner Join was a feature of 9i:

http://www.geekinterview.com/question_details/22717

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

This part of your query does not make sense

GROUP BY
S.CLCL_ID
, S.CLST_SEQ_NO
, S.CLST_STS_DTM) ST
ON ST.CLCL_ID = CLM.CLCL_ID
AND ST.CLST_SEQ_NO = CLM.CLST_SEQ_NO

It does not appear to be part of the preceding Sub query, and you have not joined declared S any where in the query.

Also you have a closing ) and no corresponding open (

Ian