Forum Stats

  • 3,838,263 Users
  • 2,262,349 Discussions
  • 7,900,568 Comments

Discussions

Pl/sql to check if the record exits already in the table , if yes then find the max salary ?

AS08
AS08 Member Posts: 19 Green Ribbon

What is wrong with this -

set serveroutput on 

Declare

app number;

BEGIN

 If EXISTS(SELECT * employee_id from employees where manager_id=100) THEN

 Select max(salary)into app from employees;

 dbms_output.put_line(app);

 End IF;

end;


Error Output -

Declare

app number;

BEGIN

 If EXISTS(SELECT * employee_id from employees where manager_id=100) THEN

 Select max(salary)into app from employees;

 dbms_output.put_line(app);

 End IF;

end;

Error report -

ORA-06550: line 4, column 21:

PLS-00103: Encountered the symbol "EMPLOYEE_ID" when expecting one of the following:


  from

ORA-06550: line 4, column 68:

PLS-00103: Encountered the symbol ")" when expecting one of the following:


  * & - + ; / at for mod remainder rem <an exponent (**)> and

  or group having intersect minus order start union where

  connect || multiset

06550. 00000 - "line %s, column %s:\n%s"

*Cause:  Usually a PL/SQL compilation error.

*Action:


Please advise - How to check if the record exists or not whose manager id = 100 , if yes then it should select the max salary from the records whose manager id = 100 and store it into a variable?

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond
    Answer ✓

    Hi, @AS08

    Using the ideas already posted, you can do something like this:

    ...
    SELECT  COUNT (*), MAX (salary)
    INTO 	num_found, max_salary
    FROM	employees
    WHERE 	manager_id = 100;
    
    IF num_found = 0
    THEN
      dbms_output.put_line ('No data found.');
    ELSE
      new_sal := max_salary + 1;
      ...
    END IF;
    ...
    

    You only need a separate num_found variable if salary can be NULL; otherwise, max_salary will be NULL if and only if no data was found.

    AS08

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,122 Red Diamond
    SELECT * employee_id from employees where manager_id=100
    


    What does "select * employee_id" actually mean in normal syntax?

    Read it to yourself "select all the columns aliased as employee_id..."

    You can't select all the columns and give them all an alias name.

    Perhaps you just meant

    "select employee_id ..."

    ?

  • BluShadow
    BluShadow Member, Moderator Posts: 42,122 Red Diamond

    Personally, I wouldn't even pre-check for the existence of records, it's just additional querying that's not needed.

    e.g.

    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    app number;
      3  begin
      4    select max(sal)
      5    into   app
      6    from   emp
      7    where  deptno = &deptno;
      8    dbms_output.put_line(case when app is null then 'No employees' else 'Max Salary: '||to_char(app) end);
      9* end;
    SQL> /
    Enter value for deptno: 40
    old   7:   where  deptno = &deptno;
    new   7:   where  deptno = 40;
    No employees
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    Enter value for deptno: 20
    old   7:   where  deptno = &deptno;
    new   7:   where  deptno = 20;
    Max Salary: 3000
    
    PL/SQL procedure successfully completed.
    


    You can tell from the result of there were no employees for the department because it will be a null value returned in this case.

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 184 Gold Badge

    EXIST is not a PL/SQL function: you must use it in SQL

    the combination select * employee_id is not valid thus select employee_id


    Just store the max salary: if there are no records the stored value will be null

    Declare

    app number;

    BEGIN

     Select max(salary) into app from employees where manager_id=100;

     dbms_output.put_line(app);

     end;

    if you still want the condition of existing records

    Declare

    cnt number;

    app number;

    BEGIN

     Select count(1) cnt, max(salary) into app from employees where manager_id=100;

     if cnt 0 then

    dbms_output.put_line(app);

    end if; 

    end;

  • AS08
    AS08 Member Posts: 19 Green Ribbon

    Hi @BluShadow ,

    the query is - Select * from employee where manager_id=100.

    earlier it was a typo mistake.

    suppose now- I have extracted the max salary from employer table with where condition matched , after that We have also stored the max value in a app variable , but now what i am asked to do is that and why i was using if condition coz - I have to check if that particular record exists in the table already then extract the max salary and then store the extracted max salary and increment that max salary value by 1 and store the incremented value in a variable for future use and if the record does not exists then simply say record does not exists.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond
    Answer ✓

    Hi, @AS08

    Using the ideas already posted, you can do something like this:

    ...
    SELECT  COUNT (*), MAX (salary)
    INTO 	num_found, max_salary
    FROM	employees
    WHERE 	manager_id = 100;
    
    IF num_found = 0
    THEN
      dbms_output.put_line ('No data found.');
    ELSE
      new_sal := max_salary + 1;
      ...
    END IF;
    ...
    

    You only need a separate num_found variable if salary can be NULL; otherwise, max_salary will be NULL if and only if no data was found.

    AS08