Re: Best match select query
Thanks for your support, the queries given didnt work as well :(
I tried the below function... but keep getting errors...
CREATE FUNCTION BestMatch (msisdn INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE var INT(6);
DECLARE s FLOAT(9,3);
DECLARE msisdn_substr8 VARCHAR(16);
SET msisdn_substr8 = SUBSTRING ( msisdn ,1 , 8 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr8');
IF var = 1 THEN
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr8');
RETURN s;
LEAVE;
END IF ;
IF var > 1 THEN
DECLARE msisdn_substr9 VARCHAR(16);
SET msisdn_substr9 = SUBSTRING ( msisdn ,1 , 9 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr9');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr9');
RETURN s;
LEAVE;
end if;
if var = 0 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr8');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr10 VARCHAR(16);
SET msisdn_substr10 = SUBSTRING ( msisdn ,1 , 10 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr10');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr10');
RETURN s;
LEAVE;
end if;
if var = 0 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr9');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr11 VARCHAR(16);
SET msisdn_substr11 = SUBSTRING ( msisdn ,1 , 11 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr11');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr11');
RETURN s;
LEAVE;
end if;
if var = 0 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr10');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr12 VARCHAR(16);
SET msisdn_substr12 = SUBSTRING ( msisdn ,1 , 12 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr12');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr12');
RETURN s;
LEAVE;
end if;
if var = 0 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr11');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr13 VARCHAR(16);
SET msisdn_substr13 = SUBSTRING ( msisdn ,1 , 13 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr13');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr13');
RETURN s;
LEAVE;
end if;
if var = 0 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr12');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr14 VARCHAR(16);
SET msisdn_substr14 = SUBSTRING ( msisdn ,1 , 14 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr14');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr14');
RETURN s;
LEAVE;
end if;
if var = 0 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr13');
RETURN s;
LEAVE;
end if;
END IF;
IF var = 0 THEN
DECLARE msisdn_substr7 VARCHAR(16);
SET msisdn_substr7 = SUBSTRING ( msisdn ,1 , 7 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr7');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr10');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr6 VARCHAR(16);
SET msisdn_substr6 = SUBSTRING ( msisdn ,1 , 6 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr6');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr6');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr5 VARCHAR(16);
SET msisdn_substr5 = SUBSTRING ( msisdn ,1 , 5 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr5');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr5');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr4 VARCHAR(16);
SET msisdn_substr4 = SUBSTRING ( msisdn ,1 , 4 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr4');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr4');
RETURN s;
LEAVE;
end if;
DECLARE msisdn_substr3 VARCHAR(16);
SET msisdn_substr3 = SUBSTRING ( msisdn ,1 , 3 ) ;
SET var = (SELECT count(*) FROM rates WHERE prefix = 'msisdn_substr3');
if var = 1 then
SET s = (SELECT price FROM rates WHERE prefix = 'msisdn_substr3');
RETURN s;
LEAVE;;
end if;
END IF
RETURN s;
END //
DELIMITER ;
can you help ?
one IF condition is met, I want to set the variable s and then leave procedure..
Thanks