Forum Stats

  • 3,782,050 Users
  • 2,254,585 Discussions
  • 7,879,901 Comments

Discussions

Categories

Please help me. I am getting error while running the following Oracle 11.2g SQL code:

User_6TYJG
User_6TYJG Member Posts: 7 Green Ribbon
in SQL & PL/SQL

DECLARE

x NUMBER := 0;     

iSalary Number;     

iAllowance Number;     

BEGIN     

iSalary := 585.90;     

iAllowance := 58.59;     

LOOP     

x := x+1;      

INSERT INTO EmployeeTab (BasicSalary)     

select CASE WHEN GL = 'L'||x THEN 23 + (iSalary*(x-1)) END     

FROM EmployeeTab;     

INSERT INTO EmployeeTab (BasicAllowance)     

select CASE WHEN GL = 'M'||x THEN 32 + (iAllowance*(x-1)) END     

FROM EmployeeTab;     

IF x > 14 THEN     

EXIT;     

END IF;     

END LOOP;     

END; 

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,619 Red Diamond
    Accepted Answer
    https://community.oracle.com/tech/developers/discussion/4489645/please-help-me-i-am-getting-error-while-running-the-following-oracle-11-2g-sql-code


    I'm guessing that you're actually trying to update the EmployeeTab table rather than really wanting to Insert records to it. By inserting individual columns like that, it's likely you're not providing important other information to the inserted row, such as the required primary key or suchlike.

    As an update, you're probably looking for something like...

        update EmployeeTab
      set BasicSalary = case when gl like 'L%' then 23+(585.90*(to_number(substr(gl,2))-1)) else BasicSalary end
         ,BasicAllowance = case when gl like 'M%' then 32+(58.59*(to_number(substr(gl,2))-1)) else BasicAllowance end
      where regexp_like(gl, '^(L|M)(1|2|3|4|5|6|7|8|9|10|11|12|13|14)$')

    That will update all existing rows that have a GL value that is L1, L2 ... L14, or M1, M2 ... M14 and then update the BasicSalary or BasicAllowance column based on the numeric value against the L or M gl value.

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond

    Hi, @User_6TYJG

    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data.  (In this case, it looks like the table may be empty when you run the code, so you may not need any INSERT statements; just the CREATE TABLE statement.) Always post your complete Oracle version (e.g. 18.4.0.0.0).

    I am getting error while running the following Oracle 11.2g SQL code:

    What is the error? Error messages are designed to help people locate and fix the mistakes. Don't hide that valuable information from the people who want to help you. Post the complete error message (or messages; PL/SQL errors often occur in groups), including line numbers

    What you posted is PL/SQL code, not SQL code. SQL and PL/SQL are two different languages, like Latin and Pig Latin.

    · Share on TwitterShare on Facebook
  • User_6TYJG
    User_6TYJG Member Posts: 7 Green Ribbon

    When i execute the code it stuck while processing...

    I am using SQL Developer and Oracle APEX.

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,619 Red Diamond
    Accepted Answer
    https://community.oracle.com/tech/developers/discussion/4489645/please-help-me-i-am-getting-error-while-running-the-following-oracle-11-2g-sql-code


    I'm guessing that you're actually trying to update the EmployeeTab table rather than really wanting to Insert records to it. By inserting individual columns like that, it's likely you're not providing important other information to the inserted row, such as the required primary key or suchlike.

    As an update, you're probably looking for something like...

        update EmployeeTab
      set BasicSalary = case when gl like 'L%' then 23+(585.90*(to_number(substr(gl,2))-1)) else BasicSalary end
         ,BasicAllowance = case when gl like 'M%' then 32+(58.59*(to_number(substr(gl,2))-1)) else BasicAllowance end
      where regexp_like(gl, '^(L|M)(1|2|3|4|5|6|7|8|9|10|11|12|13|14)$')

    That will update all existing rows that have a GL value that is L1, L2 ... L14, or M1, M2 ... M14 and then update the BasicSalary or BasicAllowance column based on the numeric value against the L or M gl value.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond

    Hi, @User_6TYJG

    When i execute the code it stuck while processing...

    So you're not getting any error; is that right?

    In any event, post a little sample data, the results you want from that data, and explain how you get those results from the given data. In the case of a DML issue (such as INSERT or UPDATE), the INSERT statements you post should show the state of the tables before the DML starts, and the results will be what the changed table looks like after the DML is finished.

    · Share on TwitterShare on Facebook
  • User_6TYJG
    User_6TYJG Member Posts: 7 Green Ribbon

    I never though of that..

    I really appreciate.

    Thank you.

    · Share on TwitterShare on Facebook