Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
August 26th, 2011, 05:47 PM
 akpaga
Registered User

Join Date: Aug 2011
Posts: 1
Time spent in forums: 33 m 47 sec
Reputation Power: 0
Query help regarding cursors?

Hi friends i have a Customer_Scoring table with following fields

KEY,MAX_value,Points,Profile_ID
Distance,1(here 1 means distance <=1),100,1
Distance,1,100,2
Distance,5(here 5 means between 2 and 5) ,50,1
Distance,5,50,2
Payment,100(here 100 means between 50 and 100),100,1
Payment,100,100,2
Payment,50(here 50 means between 0-49),50,1
Payment,50,50,2
Regular,1,1000,2

there are two profile id's 1 and 2... for profile id 2 there is additional factor regular.

Customer table

Cust_name,Distance,Payment, Regular,Profile_Id
x,1,100,0,1

y,3,40,1,2

Based on the above data i need to find out the score for x and y customer

Query inside function I wrote

Code:
```select Points
into v_Distance_Score
from Customer_Scoring
where (max_value in (select min(max_value)
from Customer_Scoring
where max_value >=v_Distance and key ='Distance')
and key ='Distance'); -- v_distance i pass the values from Customer table

select Points
into v_Payment
from Customer_Scoring
where (max_value in (select min(max_value)
from Customer_Scoring
where max_value >=v_Payment and key ='Payment')
and key ='Payment');

Total = v_Distance_Score +v_Payment

For profile_Id =2
Total = v_Distance_Score +v_Payment +V_regular

```

my boss says use the cursor to loop through all the factors for one profile_id as something below instead of writing separate sql statements for each factor which is hard coding..
Code: [Select all] [Show/ hide]

Code:
```CURSOR c_factors IS
select * from Customer_Scoring where Profile_ID =@Profile_Id
r_factors c_remarks%ROWTYPE;```

then loop through it for various factors...i am still learning cursors so thought of asking your help...

Hope i am clear....Sorry if this causes any confusion...

i am very new to Oracle...

 Viewing: Dev Shed Forums > Databases > Oracle Development > Query help regarding cursors?