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...
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
