List:General Discussion« Previous MessageNext Message »
From:Pau Marc Munoz Torres Date:November 13 2007 2:31pm
Subject:indexing tables using my owns functions
View as plain text  
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@stripped


-- 
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@stripped

Thread
indexing tables using my owns functionsPau Marc Munoz Torres13 Nov
  • indexing tables using my owns functionsPau Marc Munoz Torres13 Nov
Re: indexing tables using my owns functionsMartijn Tonies13 Nov
  • Re: indexing tables using my owns functionsPau Marc Munoz Torres13 Nov
Re: indexing tables using my owns functionsMartijn Tonies13 Nov