Need Help with SQL Code

1

New to SQL and need help. Using Oracle-TOAD. Get an error: ORA-00933 command not properly ended.

select DISTINCT clm_id
,SUM(clm_pd_amt) AS c_p_a
,MAX(r_date) AS ran_date
,MIN(clm_stat) AS c_stat
FROM i_mra.cbn
WHERE clm_stat in (5,8)
AND clm_type IN ('r', 'p')
AND svc_date between '20190101' and '20191231'
AND pd_date >= '20190101'
AND pd_date <= '20200131'
AND year = 2019
GROUP BY clm_id, clm_pd_amt, r_date, clm_stat

2

This is a forum for SQL Server - and you are using Oracle. We can try to help...

You probably need to end the query with a semi-colon which is a required statement terminator, depending on the client being used.

3

Adding semi-colon did not fix issue. Thanks for your response!

4

Looking up that error - it states that error is generated when the query ends with an inappropriate statement. They reference using an ORDER BY in a view or insert as examples.

A couple of things about your query:

  1. You have DISTINCT and GROUP BY - since you are using SUM/MAX/MIN you don't need the DISTINCT in the statement. The GROUP BY will give you distinct values based on the grouping.
  2. You have clm_id as the only grouping column, but you included clm_pd_amt, r_date and clm_stat in the GROUP BY clause. Remove those and only group by clm_id - since you want the aggregate values for the other columns.
  3. You use between for svc_date and >= and <= for pd_date. If these columns are defined as DATE data type then you can use between for both - if they are defined as datetime2 then you would be better served using an open-interval range (e.g. pd_date >= '20190101' and pd_date < '20200201').
5

Hi Jeff, followed your instructions and still received the same error: ORA-00933

6

Have you tried an Oracle forum?
Or googled this issue?

https://www.techonthenet.com/oracle/errors/ora00933.php

7

I would recommend going to a Toad forum - this is a client issue unless the query you are actually using is not what you have posted.