StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The problem is with your SQL statement:</p> <blockquote> <p>You have an error in your SQL syntax; check the manual ... for the right syntax to use near</p> <p>'<code>IN ZipCode varchar()) RETURNS</code>'</p> </blockquote> <p>I would recommend running the SQL through PhpMyAdmin or on the command line until you get it right, then run it through Magento. This man page describes the syntax of <code>CREATE FUNCTION</code>: <a href="http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html</a>. When testing stored procedures /functions in the mysql client (or PhpMyAdmin) be sure to <a href="http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html" rel="nofollow">change the delimiter</a> so that the semicolons in your function body are interpreted correctly. </p> <p>The below SQL worked for me. The things I changed from your original statement are:</p> <ol> <li><code>IN</code> is not allowed in function declarations <code>(IN ZipCode varchar())</code></li> <li>I was required to explicitly state length of the <code>varchar</code></li> <li>The <code>DECLARE</code> belongs inside the function</li> <li>I am guessing that your function is <code>DETERMINISTIC</code>, meaning it will always produce the same results for the same input parameters. If this is not the case, remove <code>DETERMINISTIC</code> from the <code>RETURNS</code> line</li> </ol> <p>Give this a shot:</p> <pre><code>DROP FUNCTION IF EXISTS {$this->getTable('fn_Get_HardinessZone')}; CREATE FUNCTION {$this->getTable('fn_Get_HardinessZone')} (ZipCode VARCHAR(15)) RETURNS INTEGER DETERMINISTIC BEGIN DECLARE result INTEGER; SELECT MAX(Zone) INTO result FROM AMI_zones WHERE (Hfzip <= LEFT(ZipCode, 5)) AND (Htzip >= LEFT(ZipCode, 5)); IF result IS NULL OR result < 1 OR (result > 11 AND result <> 99) THEN /* if the left most character is alpha, then set the zone to 98 for Canada */ IF LEFT(ZipCode, 1) >= 'A' AND LEFT(ZipCode, 1) <= 'Z' THEN SET result = 98; ELSE SET result = 99; END IF; END IF; RETURN result; END; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload