what is wrong with this mysql function

Question

DELIMITER //
    CREATE FUNCTION F_offshorePrice1(topsbwynum INTEGER, hulsbwynum INTEGER,sbwynum INTEGER, adjprice FLOAT(11,2), 
    price FLOAT(11,2), estprice FLOAT(11,2), pricehulint FLOAT(11,2), pricetopint FLOAT(11,2), pricehulcons FLOAT(11,2),
    pricetop FLOAT(11,2),priceint FLOAT(11,2),pricetht FLOAT(11,2) )
        RETURNS float(20,2)
        NOT DETERMINISTIC
        CONTAINS SQL
       SQL SECURITY DEFINER
    BEGIN
    
     DECLARE FinalPrice VARCHAR(20); 
     DECLARE err VARCHAR(20); 
    
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;
    
    
    
    IF(sbwynum > 0 AND hulsbwynum > 0 and sbwynum != hulsbwynum AND (topsbwynum = sbwynum OR topsbwynum < 1 or topsbwynum IS NULL)) THEN
      IF (pricetopint > 0) THEN
         SET FinalPrice = pricetopint;
       ELSEIF(price > 0)THEN
         SET FinalPrice = price - pricehulcons;
       ELSEIF(estprice > 0)THEN
         SET FinalPrice = estprice - pricehulcons;
        END IF;
    
    END IF;
    
    
    
    IF(sbwynum > 0 AND topsbwynum > 0 and sbwynum != topsbwynum AND (hulsbwynum = sbwynum OR hulsbwynum < 1 or hulsbwynum IS NULL)) THEN
    IF(pricehulint > 0)THEN
       SET FinalPrice = pricehulint;
     ELSEIF(price > 0)THEN
      SET FinalPrice = price - pricetop;
    ELSEIF(s.estprice > 0)THEN
      SET FinalPrice = estprice - pricetop;
    END IF;
    
    END IF; 
    
    
    IF(sbwynum > 0 AND topsbwynum != hulsbwynum AND hulsbwynum != sbwynum AND topsbwynum != sbwynum AND topsbwynum > 0 and hulsbwynum > 0 )THEN
    IF(priceint > 0)THEN
      SET FinalPrice = priceint;
    ELSEIF(price > 0)THEN
      SET FinalPrice = price - (pricetop + pricehulcons);
    ELSEIF(estprice > 0)THEN
      SET FinalPrice = estprice - (pricetop + pricehulcons);
    END IF;
    
    END IF; 
    
    
    IF(sbwynum > 0 AND topsbwynum = hulsbwynum AND sbwynum != hulsbwynum AND hulsbwynum > 0 )THEN
    IF(priceint > 0)THEN
      SET FinalPrice = priceint;
    ELSEIF(price > 0)THEN
      SET FinalPrice = price - pricetht;
    ELSEIF(estprice > 0)THEN
      SET FinalPrice = estprice - pricetht;
    END IF;
    
    END IF; 
    
    
    IF(sbwynum > 0)THEN
    IF(FinalPrice > 0)THEN
      RETURN FinalPrice;
    ELSEIF(adjprice > 0)THEN 
       RETURN adjprice;
    ELSEIF(price > 0)THEN
       RETURN price;
    ELSEIF(estprice > 0)THEN
       RETURN estprice; 
    ELSE 
       RETURN 0; 
    END IF;
    ELSE 
      RETURN 0; 
    END IF;
    
    
    END //
    
    DELIMITER ;

ERROR :=

Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unclosed quote @ 16 STR: SQL: CREATE FUNCTIONF_offshorePrice1(topsbwynum INTEGER, hulsbwynum INTEGER,sbwynum INTEGER, adjprice FLOAT(11,2), price FLOAT(11,2), estprice FLOAT(11,2), pricehulint FLOAT(11,2), pricetopint FLOAT(11,2), pricehulcons FLOAT(11,2), pricetop FLOAT(11,2),priceint FLOAT(11,2),pricetht FLOAT(11,2) ) RETURNS float(20,2) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'THIS FUNCTION WILL USE FOR OFFSHORE PRICING SYSTEM REFERS TO MAIL ID := 40516' BEGIN

DECLARE FinalPrice VARCHAR(20); DECLARE err VARCHAR(20);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;

IF(sbwynum > 0 AND hulsbwynum > 0 and sbwynum != hulsbwynum AND (topsbwynum = sbwynum OR topsbwynum < 1 or topsbwynum IS NULL)) THEN IF (pricetopint > 0) THEN SET FinalPrice = pricetopint; ELSEIF(price > 0)THEN SET FinalPrice = price - pricehulcons; ELSEIF(estprice > 0)THEN SET FinalPrice = estprice - pricehulcons; END IF;

END IF;

IF(sbwynum > 0 AND topsbwynum > 0 and sbwynum != topsbwynum AND (hulsbwynum = sbwynum OR hulsbwynum < 1 or hulsbwynum IS NULL)) THEN IF(pricehulint > 0)THEN SET FinalPrice = pricehulint; ELSEIF(price > 0)THEN SET FinalPrice = price - pricetop; ELSEIF(s.estprice > 0)THEN SET FinalPrice = estprice - pricetop; END IF;

END IF;

IF(sbwynum > 0 AND topsbwynum != hulsbwynum AND hulsbwynum != sbwynum AND topsbwynum != sbwynum AND topsbwynum > 0 and hulsbwynum > 0 )THEN IF(priceint > 0)THEN SET FinalPrice = priceint; ELSEIF(price > 0)THEN SET FinalPrice = price - (pricetop + pricehulcons); ELSEIF(estprice > 0)THEN SET FinalPrice = estprice - (pricetop + pricehulcons); END IF;

END IF;

IF(sbwynum > 0 AND topsbwynum = hulsbwynum AND sbwynum != hulsbwynum AND hulsbwynum > 0 )THEN IF(priceint > 0)THEN SET FinalPrice = priceint; ELSEIF(price > 0)THEN SET FinalPrice = price - pricetht; ELSEIF(estprice > 0)THEN SET FinalPrice = estprice - pricetht; END IF;

END IF;

IF(sbwynum > 0)THEN IF(FinalPrice > 0)THEN RETURN FinalPrice; ELSEIF(adjprice > 0)THEN RETURN adjprice; ELSEIF(price > 0)THEN RETURN price; ELSEIF(estprice > 0)THEN RETURN estprice; ELSE RETURN 0; END IF; ELSE RETURN 0; END IF;

END //

DELIMITER ;

SQL query:

CREATE FUNCTION `F_offshorePrice1(topsbwynum INTEGER, hulsbwynum INTEGER,sbwynum INTEGER, adjprice FLOAT(11,2), price FLOAT(11,2), estprice FLOAT(11,2), pricehulint FLOAT(11,2), pricetopint FLOAT(11,2), pricehulcons FLOAT(11,2), pricetop FLOAT(11,2),priceint FLOAT(11,2),pricetht FLOAT(11,2) ) RETURNS float(20,2) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'THIS FUNCTION WILL USE FOR OFFSHORE PRICING SYSTEM REFERS TO MAIL ID := 40516' BEGIN DECLARE FinalPrice VARCHAR(20); DECLARE err VARCHAR(20); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1; IF(sbwynum > 0 AND hulsbwynum > 0 and sbwynum != hulsbwynum AND (topsbwynum = sbwynum OR topsbwynum < 1 or topsbwynum IS NULL)) THEN IF (pricetopint > 0) THEN SET FinalPrice = pricetopint; ELSEIF(price > 0)THEN SET FinalPrice = price - pricehulcons; ELSEIF(estprice > 0)THEN SET FinalPrice = estprice - pricehulcons; END IF; END IF; IF(sbwynum > 0 AND topsbwynum > 0 and sbwynum != topsbwynum AND (hulsbwynum = sbwynum OR hulsbwynum < 1 or hulsbwynum IS NULL)) THEN IF(pricehulint > 0)THEN SET FinalPrice = pricehulint; ELSEIF(price > 0)THEN SET FinalPrice = price - pricetop; ELSEIF(s.estprice > 0)THEN SET FinalPrice = estprice - pricetop; END IF; END IF; IF(sbwynum > 0 AND topsbwynum != hulsbwynum AND hulsbwynum != sbwynum AND topsbwynum != sbwynum AND topsbwynum > 0 and hulsbwynum > 0 )THEN IF(priceint > 0)THEN SET FinalPrice = priceint; ELSEIF(price > 0)THEN SET FinalPrice = price - (pricetop + pricehulcons); ELSEIF(estprice > 0)THEN SET FinalPrice = estprice - (pricetop + pricehulcons); END IF; END IF; IF(sbwynum > 0 AND topsbwynum = hulsbwynum AND sbwynum != hulsbwynum AND hulsbwynum > 0 )THEN IF(priceint > 0)THEN SET FinalPrice = priceint; ELSEIF(price > 0)THEN SET FinalPrice = price - pricetht; ELSEIF(estprice > 0)THEN SET FinalPrice = estprice - pricetht; END IF; END IF; IF(sbwynum > 0)THEN IF(FinalPrice > 0)THEN RETURN FinalPrice; ELSEIF(adjprice > 0)THEN RETURN adjprice; ELSEIF(price > 0)THEN RETURN price; ELSEIF(estprice > 0)THEN RETURN estprice; ELSE RETURN 0; END IF; ELSE RETURN 0; END IF; END // DELIMITER ;

MySQL said:

1059 - Identifier name 'F_offshorePrice1(topsbwynum INTEGER, hulsbwynum INTEGER,sbwynum INTEGER, adjprice FLOAT(11,2),

pri' is too long

Can anyone please tell me what's wrong with this user definded function in mysql 5.1 using cpanel bUT IN EMS MANAGER 2007 ITS RUNNING CORRECT. Thanks a lot...

Are you getting an error message? If so, what's the error message? Or is it just not working the way you expect? If so, what is it not doing that you are expecting? — Trott

Adrian Schmidt Adrian Schmidt · Accepted Answer · 2011-05-17T06:05:17

First line:

CREATE FUNCTION `F_offshorePrice1(topsbwynum INTEGER...

Try:

CREATE FUNCTION F_offshorePrice1(topsbwynum INTEGER...

Or, in case the ` character should be there, it doesn't seem to have a matching closing character...

what is wrong with this mysql function

1059 - Identifier name 'F_offshorePrice1(topsbwynum INTEGER, hulsbwynum INTEGER,sbwynum INTEGER, adjprice FLOAT(11,2),

1 Answers