MySQL Forums
Forum List  »  Newbie

Re: Best match select query
Posted by: Yahya AlNajjar
Date: September 11, 2016 02:57AM

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

Options: ReplyQuote


Subject
Written By
Posted
September 10, 2016 06:10AM
September 10, 2016 08:17AM
September 10, 2016 08:59AM
September 10, 2016 09:32AM
September 10, 2016 11:01PM
Re: Best match select query
September 11, 2016 02:57AM
September 11, 2016 11:18AM
September 11, 2016 01:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.