Home » SQL & PL/SQL » SQL & PL/SQL » Cursor (Oracle 8i)
Cursor [message #387182] Wed, 18 February 2009 03:30 Go to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Dear Experts,
Please Help me to write this Interesting Cursor.

Scenario::

Table :Shop Sequence
Cloumns: Depot_Code,Route_Code,Shop_Sequnce,Service_Day

Problem:Need Updation
Desc: Dear All.. Data is goes like that

Depot Code        Route Code       Service Day     Shop Sequnce         
800               200              1   
800               201              1      
800               202              1       
800               203              1       
800               204              1       
800               205              1       
800               206              1       
800               207              1  
800               200              2   
800               201              2      
800               202              2       
800               203              2       
800               204              2       
800               205              2       
800               206              2       
800               207              3
800               200              3   
800               201              3      
800               202              3       
800               203              3       
800               204              3       
800               205              3       
800               206              3       
800               207              3

Desired Requirements

Depot Code        Route Code       Service Day     Shop Sequnce         
800               200              1   			1
800               201              1      		2	
800               202              1       		3	
800               203              1       		4	
800               204              1       		5
800               205              1       		6
800               206              1       		7
800               207              1  			8
800               200              2   			1
800               201              2      		2	
800               202              2       		3
800               203              2       		4
800               204              2       		5
800               205              2       		6
800               206              2       		7
800               207              3			1
800               200              3   			2
800               201              3      		3	
800               202              3       		4
800               203              3       		5
800               204              3       		6
800               205              3       		7
800               206              3       		8
800               207              3                 	9


Everytime the Shop Sequnce should be Updated with the Counting No. Till The Service Day goes same. And Break when Service Day Changes and reset to 1 and Update again with Counting.

Javed

[Updated on: Wed, 18 February 2009 03:33]

Report message to a moderator

Re: Cursor [message #387191 is a reply to message #387182] Wed, 18 February 2009 03:58 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@javed.khan,

Please post what you have tried so far and explain where you are facing the issue in achieving your requirement.

You may find the following link helpful:
Performing SQL Operations from PL/SQL
[***Added***]
Also, chekout functions like ROW_NUMBER, RANK and DENSE_RANK in Analytic functions by Example

Regards,
Jo

[Updated on: Wed, 18 February 2009 04:02]

Report message to a moderator

Re: Cursor [message #387192 is a reply to message #387182] Wed, 18 February 2009 04:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
An analytic function is the easy way out:
select depot_cd
     , route_cd
     , service_day
     , row_number() over ( partition by service_day order by route_cd) x
from yourtable
order by service_day, route_cd
But, if you want to update the table and store the values in the database you have to wrap the row_number() in an inline view (inner select). You cannot use windowing functions directly in an update.

Does that help?

MHE
Re: Cursor [message #387196 is a reply to message #387192] Wed, 18 February 2009 04:12 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Thanx Maher,

Its Done

U Rock.
Cool
Re: Cursor [message #387197 is a reply to message #387192] Wed, 18 February 2009 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Cursor [message #387204 is a reply to message #387197] Wed, 18 February 2009 04:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It wasn't a complete solution as I didn't provide the update statement, Michel.

The Forum Guide is out of date, by the way, since there is no 'newbie section' anymore. Wink

MHE
Re: Cursor [message #387210 is a reply to message #387204] Wed, 18 February 2009 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It wasn't a complete solution as I didn't provide the update statement, Michel.

Yes, good one! Wink

Regards
Michel
Re: Cursor [message #387216 is a reply to message #387210] Wed, 18 February 2009 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
you could meet the OP's requirement with:
UPDATE table
SET shop_sequence = route_code-199
Re: Cursor [message #387217 is a reply to message #387216] Wed, 18 February 2009 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That is the best one. ./fa/2115/0/

Regards
Michel
Re: Cursor [message #387221 is a reply to message #387217] Wed, 18 February 2009 05:40 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Wow...Really Great Clue's and tricks...
Thanks alott guys...
Previous Topic: packages
Next Topic: Result of quesry
Goto Forum:
  


Current Time: Fri Dec 02 22:59:26 CST 2016

Total time taken to generate the page: 0.09745 seconds