ORA 00923 - From Keyword not found where expected 2

[IngDep]

Continually receive the ORA 00923 error upon trying to run
the following script via Oracle Sql Developer to extract data from Oracle database.

Error appears to be caused by the line that begins with
the subquery.

Any assistance greatly appreciated considering that after 45 minutes of review and modification of the sql, the error persists.

SELECT
C.GRGR_CK,
C.CLCL_ID,
SUM(CL.CDML_CHG_AMT),
SUM(CL.CDML_PAID_AMT),
ST.Adjudicated_Date,
M.MEME_CK,
M.MEME_LAST_NAME,
M.MEME_FIRST_NAME,
P.PRPR_ID,
P.PRPR_NAME
FROM
GOAL.CMC_CLCL_CLAIM C
INNER JOIN GOAL.CMC_CDML_CL_LINE CL
ON C.CLCL_ID = CL.CLCL_ID
INNER JOIN GOAL.CMC_CLST_STATUS S
INNER JOIN
(SELECT
S.CLCL_ID,
MAX(S.CLST_STS_DTM)AS Adjudicated_Date
AND S.CLST_STS = '01'
AND S.USUS_ID <> 'GCO'
GROUP BY
S.CLCL_ID) ST
ON ST.CLCL_ID = C.CLCL_ID
INNER JOIN GOAL.CMC_MEME_MEMBER M
ON C.MEME_CK = M.MEME_CK
INNER JOIN GOAL.CMC_PRPR_PROV P
ON C.PRPR_ID = P.PRPR_ID
WHERE
C.GRGR_CK = '20'
GROUP BY
C.GRGR_CK,
C.CLCL_ID,
CL.CDML_CHG_AMT,
CL.CDML_PAID_AMT,
M.MEME_CK,
M.MEME_LAST_NAME,
M.MEME_FIRST_NAME,
P.PRPR_ID,
P.PRPR_NAME,
ST.Adjudicated_Date
HAVING
SUM(CL.CDML_PAID_AMT) >= 5000

 

[SantaMufasa]

IngDep,

Troubleshooting becomes much easier when one uses "good-form" coding standards:

SELECT C.GRGR_CK
      ,C.CLCL_ID
      ,SUM(CL.CDML_CHG_AMT)
      ,SUM(CL.CDML_PAID_AMT)
      ,ST.Adjudicated_Date
      ,M.MEME_CK
      ,M.MEME_LAST_NAME
      ,M.MEME_FIRST_NAME
      ,P.PRPR_ID
      ,P.PRPR_NAME
  FROM GOAL.CMC_CLCL_CLAIM C
       INNER JOIN GOAL.CMC_CDML_CL_LINE CL ON C.CLCL_ID = CL.CLCL_ID
       INNER JOIN GOAL.CMC_CLST_STATUS S
       INNER JOIN (SELECT S.CLCL_ID
                         ,MAX(S.CLST_STS_DTM)AS Adjudicated_Date
                    <Missing "FROM" and "WHERE">
                      AND S.CLST_STS = '01'
                      AND S.USUS_ID <> 'GCO'
                    GROUP BY S.CLCL_ID) ST
                  ON ST.CLCL_ID = C.CLCL_ID
       INNER JOIN GOAL.CMC_MEME_MEMBER M ON C.MEME_CK = M.MEME_CK
       INNER JOIN GOAL.CMC_PRPR_PROV P ON C.PRPR_ID = P.PRPR_ID
 WHERE C.GRGR_CK = '20'
 GROUP BY C.GRGR_CK
      ,C.CLCL_ID
      ,CL.CDML_CHG_AMT
      ,CL.CDML_PAID_AMT
      ,M.MEME_CK
      ,M.MEME_LAST_NAME
      ,M.MEME_FIRST_NAME
      ,P.PRPR_ID
      ,P.PRPR_NAME
      ,ST.Adjudicated_Date
HAVING SUM(CL.CDML_PAID_AMT) >= 5000

Notice that the SQL parser is correct...you are missing both a FROM and a WHERE clause, above, as indicated by '<Missing "FROM" and "WHERE">'.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[IngDep]

The error persists.

 

[karluk]

If you really are trying to get this sql to work on an Oracle 8i database, you will have to do a lot more than just tweak the syntax a little. As I recall, Oracle 8i didn't support the "inner join" syntax.