indexing tables using my owns functions 2007-11-13 - By Pau Marc Munoz Torres
Back
Hi
I've created a function that return a float value the code for it is :
create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7
varchar(20),pin9 varchar(20),MOL varchar(20)) returns float
DETERMINISTIC
begin
declare output float;
declare P1 float;
declare P4 float;
declare P6 float;
declare P7 float;
declare P9 float;
select VALUE into P1 from PSSMS where AA=pin1 and POS='1'
and MOLEC=MOL;
select VALUE into P4 from PSSMS where AA=pin4 and POS='4'
and MOLEC=MOL;
select VALUE into P6 from PSSMS where AA=pin6 and POS='6'
and MOLEC=MOL;
select VALUE into P7 from PSSMS where AA=pin7 and POS='7'
and MOLEC=MOL;
select VALUE into P9 from PSSMS where AA=pin9 and POS='9'
and MOLEC=MOL;
select P1+P4+P6+P7+P9 into output;
return output;
end
//
And it works, now, i would like index a table using this function.
The table description is:
mysql> describe precalc;
+-- ----+-- ------+-- ---+-- --+-- ------+-- ---- ---- ---+
| Field | Type | Null | Key | Default | Extra |
+-- ----+-- ------+-- ---+-- --+-- ------+-- ---- ---- ---+
| id | int(6) | NO | PRI | NULL | auto_increment |
| P1 | char(1) | YES | | NULL | |
| P4 | char(1) | YES | | NULL | |
| P6 | char(1) | YES | | NULL | |
| P7 | char(1) | YES | | NULL | |
| P9 | char(1) | YES | | NULL | |
+-- ----+-- ------+-- ---+-- --+-- ------+-- ---- ---- ---+
6 rows in set (0.01 sec)
and i try index by the following command:
mysql> create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13'));
But i Get the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1
Some one knows where is the error?
Thanks
Pau
--
Pau Marc Mu?oz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
tel?fon: 93 5812807
Email : paumarc.munoz@(protected)
--
Pau Marc Mu?oz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
tel?fon: 93 5812807
Email : paumarc.munoz@(protected)
|
|
