Home » SQL & PL/SQL » SQL & PL/SQL » updating the table and putting the salary in desc order (Sql * Plus: Release 9.0.1.0.1)
updating the table and putting the salary in desc order [message #378399] Tue, 30 December 2008 04:45 Go to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi all,

want to update the salary in descending order. text file is attached.

Regards,
Asutosh
Re: updating the table and putting the salary in desc order [message #378406 is a reply to message #378399] Tue, 30 December 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want help, explain what you want to do INLINE and not in attached file.

Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: updating the table and putting the salary in desc order [message #378414 is a reply to message #378406] Tue, 30 December 2008 05:38 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does "the code to do the same is under procedure name salary_desc but it is working." mean? If it is working, what's the problem? If, on the other hand, it is NOT working, what happens when you run it?

On a first glance, it is doomed to a failure as you are using "r1.empno" in the UPDATE statement, but there's no "empno" column in list of selectable columns of a cursor declaration.

WHEN OTHERS exception handler is useless; search the Forum and you'll find out why as there have been far too many examples.

So, how about creating a complete test case, including a working (or, should I rather say, compiling) code?
Re: updating the table and putting the salary in desc order [message #378563 is a reply to message #378414] Tue, 30 December 2008 23:49 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
i wrote the procedure

before executing procedure:
select * from test;

     EMPNO NAME           SALARY
---------- ---------- ----------
         1 ashu             4000
         2 bhawana          6000
         3 raj              2000

after executing the result is:
   EMPNO NAME           SALARY
-------- ---------- ----------
       1 ashu             2000
       2 bhawana          2000
       3 raj              2000

desired:
   EMPNO NAME           SALARY
-------- ---------- ----------
       1 ashu             6000
       2 bhawana          4000
       3 raj              2000


the procedure i wrote is;
CREATE OR REPLACE PROCEDURE Salary_desc
IS
  v_empNo   NUMBER;
  v_Salary  NUMBER;
  CURSOR c1 IS 
    SELECT   Salary
    FROM     Test
    ORDER BY Salary DESC;
  CURSOR c2 IS 
    SELECT   empNo
    FROM     Test
    ORDER BY empNo;
BEGIN
  FOR r1 IN c1 LOOP
    v_Salary := r1.Salary;
    
    dbms_Output.Put_Line(r1.Salary);
    
    FOR r2 IN c2 LOOP
      dbms_Output.Put_Line(r2.empNo);
      
      v_empNo := r2.empNo;
      
      UPDATE Test
      SET    Salary = v_Salary
      WHERE  empNo = v_empNo;
    END LOOP;
    
    dbms_Output.Put_Line(v_empNo);
    
    dbms_Output.Put_Line(v_Salary);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_Output.Put_Line('Error');
END;

kindly let me where the change is required in the code to get the desired output. i tried to to SQL-formatter.

Regards,
Ashut

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 31 December 2008 00:41] by Moderator

Report message to a moderator

Re: updating the table and putting the salary in desc order [message #378572 is a reply to message #378563] Wed, 31 December 2008 00:24 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Assuming empno is sequence for rank of the employee name, hope this can help

CREATE TABLE TTTT ( A NUMBER,B VARCHAR2(10),C NUMBER(10))


INSERT INTO TTTT ( A, B, C ) VALUES ( 
3, 'raj', 2000); 
INSERT INTO TTTT ( A, B, C ) VALUES ( 
2, 'bhawana', 6000); 
INSERT INTO TTTT ( A, B, C ) VALUES ( 
1, 'ashu', 4000); 
COMMIT;

UPDATE TTTT A
   SET C = (SELECT C
              FROM (SELECT A, C, ROW_NUMBER () OVER (ORDER BY C DESC) RN
                      FROM TTTT) B
             WHERE A.A = B.RN)


SELECT * FROM TTTT;
         A B                   C
---------- ---------- ----------
         3 raj              2000
         2 bhawana          4000
         1 ashu             6000

3 rows selected.



Thanks
Trivendra


Re: updating the table and putting the salary in desc order [message #378688 is a reply to message #378572] Wed, 31 December 2008 05:54 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
thanks triv.

the query is working fine and got the result. but can you please explain, how it is working?

AshuT
Re: updating the table and putting the salary in desc order [message #378701 is a reply to message #378688] Wed, 31 December 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
435 posts and you are not able to split the query and analyze it! I think SQL is not your matter.

Regards
Michel
Re: updating the table and putting the salary in desc order [message #378707 is a reply to message #378701] Wed, 31 December 2008 09:52 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel
435 posts and you are not able to split the query and analyze it! I think SQL is not your matter.


./fa/5529/0/
  • Attachment: ab_ocp.PNG
    (Size: 14.33KB, Downloaded 176 times)
Re: updating the table and putting the salary in desc order [message #378708 is a reply to message #378707] Wed, 31 December 2008 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe learning by heart brain dumps is his matter. Wink

Regards
Michel
Re: updating the table and putting the salary in desc order [message #378909 is a reply to message #378708] Fri, 02 January 2009 09:35 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Dear Bro,

True and the forum is really interesting. See some one has said if you will ask the query then it may be clear to you but if you did not ask you will remain a fool. One should encourage the people but this way it is the opposite.

I hope that I am able to pass my feelings to you.

Bye,
Razz
Previous Topic: How to use REF OF..
Next Topic: Toggle Sequence
Goto Forum:
  


Current Time: Sat Dec 03 08:02:02 CST 2016

Total time taken to generate the page: 0.15576 seconds