Need Help with SQL Code

Question

Need Help with SQL Code

darrylmybiz

SSC Enthusiast

Points: 149
More actions
June 25, 2020 at 5:57 pm

#3765083

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

doug.brown SSCertifiable Points: 5607 More actions · Answer 1

I'm not familiar with Oracle or Toad, but maybe one of them is enforcing a semicolon terminator? Try adding a semicolon at the end and see if the error goes away.

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 2

Doing a quick google on error ORA-00933 - it sounds like that shouldn't be thrown with a SELECT statement. Is there more to the query that you did not include in this post?

Either way, my process to troubleshoot such queries in SQL Server (not the same thing as Oracle, but the tricks should still work) would be to change your query to be less complex. For example, change it to a trivial SELECT TOP(1) without the DISTINCT or the WHERE (still need the group by as you are doing aggregates). Then see if that query succeeds. If so, increase the complexity. I personally would start by adding the WHERE clauses back in all at once leaving DISTINCT out and TOP in. If it fails, then one of your WHERE clauses has a typo. If it succeeds, put the DISTINCT back in.

Alternately, you could load up the terminal version of sql to connect to Oracle and it will highlight where you have a typo. I expect Toad has that too, but I do not have Toad installed and am not familiar with it.

My source - https://www.got-it.ai/solutions/sqlquerychat/sql-help/general-sql/ora-00933-sql-command-not-properly-ended-pl-sql/

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 3

I responded to this same question on SQL Team. There is no need for both DISTINCT and GROUP BY - so remove the DISTINCT as it is just going to force an expensive sort operation.

It looks to me like the problem is in the GROUP BY - where the query is grouping on columns not included in the SELECT statement. Yes, they are included in the aggregates - but it is meaningless to group on those columns.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

saysansay Valued Member Points: 50 More actions · Answer 4

For Oracle Please try

select 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

Better if you have structure table and show what your expected result ?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

I agree. It's been a very long time since I worked with Oracle but semi-colon placement was critical.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

There is an Oracle specific forum. It might have people monitoring it who know more about Oracle.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning