I am creating a custom function to perform a commonly required task, to return a formatted equipment id number along with it’s description. I am aware that this result can be easily accomplished using joins when querying the tables wherin the usage of the equipment is recorded, but I’d like to get this working both for convenience and for my own education. The text of my function is as follows:
<code>DELIMITER //
CREATE FUNCTION AER (aer INT)
RETURNS VARCHAR(512)
BEGIN
DECLARE item VARCHAR(512);
SELECT CONCAT('AER',LPAD(`AER`,3,'0'),' - ',`Description`) INTO item FROM `t_equipment` WHERE `AER` = aer LIMIT 1;
RETURN item;
END; //
</code>
<code>DELIMITER //
CREATE FUNCTION AER (aer INT)
RETURNS VARCHAR(512)
BEGIN
DECLARE item VARCHAR(512);
SELECT CONCAT('AER',LPAD(`AER`,3,'0'),' - ',`Description`) INTO item FROM `t_equipment` WHERE `AER` = aer LIMIT 1;
RETURN item;
END; //
</code>
DELIMITER //
CREATE FUNCTION AER (aer INT)
RETURNS VARCHAR(512)
BEGIN
DECLARE item VARCHAR(512);
SELECT CONCAT('AER',LPAD(`AER`,3,'0'),' - ',`Description`) INTO item FROM `t_equipment` WHERE `AER` = aer LIMIT 1;
RETURN item;
END; //
When using the test statement:
<code>SELECT AER(1), AER(49), AER(114) FROM `t_tests`;
</code>
<code>SELECT AER(1), AER(49), AER(114) FROM `t_tests`;
</code>
SELECT AER(1), AER(49), AER(114) FROM `t_tests`;
I would expect the output:
<code>AER001 - Oertling NA 264 AER049 - Cutting Guides AER114 - Cyclic Corrosion Cabinet
</code>
<code>AER001 - Oertling NA 264 AER049 - Cutting Guides AER114 - Cyclic Corrosion Cabinet
</code>
AER001 - Oertling NA 264 AER049 - Cutting Guides AER114 - Cyclic Corrosion Cabinet
I am actually receiving:
<code>AER001 - Oertling NA 264 AER049 - Oertling NA 264 AER114 - Oertling NA 264
</code>
<code>AER001 - Oertling NA 264 AER049 - Oertling NA 264 AER114 - Oertling NA 264
</code>
AER001 - Oertling NA 264 AER049 - Oertling NA 264 AER114 - Oertling NA 264
In essence I am returning the correct AER number but the description from the first row of the table. Any ideas what is going wrong will be gratefully received.
4