Home » SQL & PL/SQL » SQL & PL/SQL » update procedure using cursor / insert rank based on year (merged 4 different posts)
update procedure using cursor / insert rank based on year (merged 4 different posts) [message #357332] Tue, 04 November 2008 23:30 Go to next message
gupta_vama
Messages: 8
Registered: October 2008
Location: hyderabad
Junior Member
i have one table that description is
promo_name varchar,
promo_subcategory varchar,
promo_category varchar,
calander_month_desc Date,
total_amount_sold,rank number,
rank number

this is table

how to insert rank column depends on month of year wise (using column(calander_month_desc)) and total amount using oracle procedur
Re: Insert rank column based on year and total amount [message #357345 is a reply to message #357332] Wed, 05 November 2008 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at RANK and DENSE_RANK functions as well as SUM in its analytic form.

Please 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) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also post a test case: create table and insert statements along with the result you want with these data.


Regards
Michel
Re: Insert rank column based on year and total amount [message #357575 is a reply to message #357332] Wed, 05 November 2008 22:07 Go to previous messageGo to next message
gupta_vama
Messages: 8
Registered: October 2008
Location: hyderabad
Junior Member
I have one table(aggrigate_promotions) that description is
promo_name varchar2(20),
promo_subcategory varchar2(20),
promo_category varchar2(30),
calander_month_desc Date,
total_amount_sold,rank number(10,3),

this is table
how to update this(rank) column with raning feature for every year for every promo_name.It means the promo_name with max(total_amount_sold) in that year will get Rank1.The next highest will get Rank2 and so on The Rank should be for a year and for promo name
Re: Insert rank column based on year and total amount [message #357609 is a reply to message #357575] Thu, 06 November 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 05 November 2008 07:30
Have a look at RANK and DENSE_RANK functions as well as SUM in its analytic form.

Please 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) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also post a test case: create table and insert statements along with the result you want with these data.


Regards
Michel


Re: Insert rank column based on year and total amount [message #357631 is a reply to message #357332] Thu, 06 November 2008 01:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
how to insert rank column depends on month of year wise (using column(calander_month_desc)) and total amount using oracle procedur


Anyway you have to use ANALYTIC functions. But why can't you create a view instead using those function instead of updating the column using the procedure ? (if your business process allows)

Smile
Rajuvan.

[Updated on: Thu, 06 November 2008 01:51]

Report message to a moderator

procedure for updation [message #359683 is a reply to message #357332] Tue, 18 November 2008 00:30 Go to previous messageGo to next message
gupta_vama
Messages: 8
Registered: October 2008
Location: hyderabad
Junior Member
i have one table(aggr_promo)
desc aggr_promo;
promo_name varchar2(20)
promo_subcategory varchar2(20)
promo_category varchar2(20)
calendar_month_desc varchar2(Cool
total_amount_sold number(10,2)
total_quantity_sold number(3)

by above table have some records are there

i am adding another one column is rank using alter statement

how to update rank column in table based on calendar_month_desc
and total_amount_sold using procedure


help me
Re: procedure for updation [message #359691 is a reply to message #359683] Tue, 18 November 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous (besides is this another question?)
Michel Cadot wrote on Wed, 05 November 2008 07:30
Please 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) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also post a test case: create table and insert statements along with the result you want with these data.


Regards
Michel


Re: procedure for updation [message #359737 is a reply to message #359683] Tue, 18 November 2008 02:04 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
I'm sure the question will arise as to what you have already tried... No point in dishing out homework, where are you getting stuck in the process of updating?
Re: procedure for updation [message #359885 is a reply to message #359683] Tue, 18 November 2008 08:01 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
gupta_vama wrote on Tue, 18 November 2008 01:30

how to update rank column in table based on calendar_month_desc
and total_amount_sold using procedure



UPDATE statement.
Re: procedure for updation [message #359888 is a reply to message #359885] Tue, 18 November 2008 08:08 Go to previous messageGo to next message
Akash83
Messages: 9
Registered: November 2008
Location: India
Junior Member
Why you want a procedure?

Just one UPDATE statement is enough
update table using procedure [message #360161 is a reply to message #357332] Wed, 19 November 2008 22:35 Go to previous messageGo to next message
gupta_vama
Messages: 8
Registered: October 2008
Location: hyderabad
Junior Member
i have table aggr_promo;
description of table
promo_name varchar2(10)
promo_subcategory varchar2(20)
promo_category varchar2(20)
calendar_month_desc varchar2(Cool
total_amount_sold number(10,2)
total_quantity_sold number(3)

this table have some records and
add one column i.e rank data type is number

how to update the rank column based on year(calendar_month_desc)
and total_amount_sold that is give to rank 1 and anothere record is rank 2,we can take another year its same like rank 1 ...
its update statement using package/procedure

please suggest me
Re: update table using procedure [message #360165 is a reply to message #360161] Wed, 19 November 2008 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 18 November 2008 07:49
From your previous (besides is this another question?)
Michel Cadot wrote on Wed, 05 November 2008 07:30
Please 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) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also post a test case: create table and insert statements along with the result you want with these data.


Regards
Michel




You can start the same topic any time you want without following the rules the only thing you do is irritating.

update procedure using cursors [message #360543 is a reply to message #357332] Fri, 21 November 2008 05:39 Go to previous messageGo to next message
gupta_vama
Messages: 8
Registered: October 2008
Location: hyderabad
Junior Member

Add one column to the aggregation table that you created called "RANK" number data type

Update this column with ranking a feature for every promo Name. I t means the promo name with max(total_amount_sold) in that year will get
RANK1, the next highest will get RANK2 and so on. the Rank should be for a year for a promoname;


In the above query you need to create a table called aggregation table and follow the rest

what we have done till now

create table aggr_promo_calmonth_sales(aggrigation table)

promo_name varchar2(20)
promo_subcate varchar2(30)
promo_category varchar2(30)
calendar_month_desc varchar2(Cool
total_quantity_sold number(7)
total_amount_sold number(10,2);



select promo_name,promo_subcategory,promo_category,calendar_month_desc,total_quantity_sold,total_amount_sold, row_number() over (partition by substr(calendar_month_desc,1,4) order by total_amount_sold) "rank" from aggr_promo_calmonth_sales order by calendar_month_desc,total_amount_sold;



the above query is a required output

now what you have to do is add a column to above table(aggr_promo_calmonth_sales) called rank and fill this column with rank.

Hints use cursors and procedures/packages.
Re: update procedure using cursors [message #360549 is a reply to message #360543] Fri, 21 November 2008 05:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 20 November 2008 06:06
Michel Cadot wrote on Tue, 18 November 2008 07:49
From your previous (besides is this another question?)
Michel Cadot wrote on Wed, 05 November 2008 07:30
Please 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) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also post a test case: create table and insert statements along with the result you want with these data.


Regards
Michel




You can start the same topic any time you want without following the rules the only thing you do is irritating.



Previous Topic: PLSQL and SQL optimization
Next Topic: dynamic sql problem
Goto Forum:
  


Current Time: Sun Dec 04 08:43:54 CST 2016

Total time taken to generate the page: 0.09594 seconds