logic not meeting requirement- PL-SQL

Posted by Madhavi under Others on 8/24/2010 | Points: 10 | Views : 1599 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

i was stuck up in writing the logic in PL-SQL with cursors. i am writing the rough code below

Procedure xyz

select * from example

for I in c2 loop
....
....
cnt:=cnt+1

if count=30 then
logic to implement
.....
.....

endif

endloop
end xyz

I want to implement some logic to implement for all records of select statement but 30 at a time .

how to implement :
if it is <30, how to implement the logic in "if condition".
Also, if count is some 60, for frist 40 records the logic will implment but for rest 20 records, the condition (count=40) will not satisfy and will not go into logic

Kindly let me know if u need any more information

Thanks
madhavi

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Ambily.raj on: 8/31/2010 [Member] [Microsoft_MVP] [MVP] Silver | Points: 25

0	You can use the Row_Number() and CTE query to achieve the same. Following sample shows that the update statement will update the ProductName of the first 4 records in the order of productID. with productBackUp as ( select ROW_NUMBER() over(order by productid) as "ProdCount",* from prodBack ) update productBackUp set productName=productName+'##' where ProdCount<5 Thanks Ambily Thanks Ambily K K http://ambilykk.com/ Madhavi, if this helps please login to Mark As Answer. \| Alert Moderator

You can use the Row_Number() and CTE query to achieve the same.

Following sample shows that the update statement will update the ProductName of the first 4 records in the order of productID.

with productBackUp

as

(

select ROW_NUMBER() over(order by productid) as "ProdCount",* from prodBack

)

update productBackUp

set productName=productName+'##'

where ProdCount<5

Thanks
Ambily

Thanks
Ambily K K
http://ambilykk.com/

Madhavi, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts