Need Help with SQL Code

  • 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

     

  • 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.

  • 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.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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 ?

     

     

     

  • 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)
    Intro to Tally Tables and Functions

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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