0

I'm writing some code but there are some errors. If the numbers are the same, to show only one record of the oldest date. Thanks for help.

The errors: SQL command not properly ended and not a single-group group function

My code in the following

    select       
       DIVISION,   
       DEPT_CODE,      
       NO,
       min(START_DATE),       
    row_number() over (order by 
       DIVISION,   
       DEPT_CODE   
    ) as "NEW_NO"
    FROM INFO 
    Group by NO
    where (TITLE IS NULL
      OR (upper(TITLE) NOT LIKE '%RESEARCH%'
      AND upper(TITLE) NOT LIKE '%POSTDOCTORAL FELLOW%'
      AND upper(TITLE) NOT LIKE '%PROJECT%'
      )
    )
4

To sum up, there are three major issues in your query:

  1. As noted by ypercubeᵀᴹ, the order of the clauses is wrong in one place: the WHERE clause should go after the FROM clause and before the GROUP BY clause:

    SELECT
      ...
    FROM
      ...
    WHERE
      ...
    GROUP BY
      ...
    
  2. As has already been addressed by Rajesh Ranjan, the GROUP BY clause must include all non-aggregated columns of your SELECT list, so it should look like this:

    GROUP BY
      DIVISION,
      DEPT_CODE,
      NO
    
  3. Finally, if your statement happens to be part of a PL/SQL block, you must end it with a semicolon:

    SELECT
      DIVISION,
      DEPT_CODE,
      NO,
      min(START_DATE),
      row_number() over (order by 
        DIVISION,   
        DEPT_CODE   
      ) as "NEW_NO"
    FROM INFO 
    WHERE (TITLE IS NULL
      OR (upper(TITLE) NOT LIKE '%RESEARCH%'
      AND upper(TITLE) NOT LIKE '%POSTDOCTORAL FELLOW%'
      AND upper(TITLE) NOT LIKE '%PROJECT%'
      )
    )
    GROUP BY
      DIVISION,
      DEPT_CODE,
      NO;
    

Note that resolving all these issues does not guarantee you will get the expected result. Addressing the above points should merely let you run your query without errors. If it shows incorrect results, that will be a separate issue requiring a separate question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.