SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Applying Equation to Decimal Values In Query

    Would it be possible to replace the ? with the price and calculate new price as a result of the calculation given the below scenario in a query? Essentially I'm looking for some way to eval() in SQL I believe…

    I have a client who would like to be able set dynamic pricing on products based on equations being applied to the distributors price of the product. However, if I handle this in the application language I loose mySQL sorting and grouping abilities that I really need to calculate price ranges and what not. So this seems like the *best* idea…

    thanks



    Code SQL:
    CREATE TABLE prices (
    	prices_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,price DECIMAL(20,2) NOT NULL DEFAULT '0.00'
    	,PRIMARY KEY(prices_id)
    ) ENGINE=MyISAM DEFAULT CHARSET = utf8;
     
    CREATE TABLE formulas (
    	formulas_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,formula VARCHAR(96) NOT NULL
    	,PRIMARY KEY(formulas_id)
    ) ENGINE=MyISAM DEFAULT CHARSET = utf8;
     
    INSERT INTO prices (price) VALUES ('20.00'),('40.00'),('60.00'),('100.00')
     
    INSERT INTO formulas (formula) VALUES ('?+20.00'),('?*2.00'),('?-5.00')
     
    SELECT
         p.price
         ,f.formula
         ,'???' AS RESULT
      FROM
         prices p
     CROSS
      JOIN
         formulas f;

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Maybe in a stored procedure, since you can concatenate strings and execute them as a query there, I don't think so in just a SELECT...

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    So this would yield exactly what I need in terms of an equation. However, I'm not certain if its even possible to execute it… Oh eval() were are thou



    Code SQL:
    SELECT
         REPLACE(f.formula,'?',p.price) AS pricing_calculation
      FROM
         prices p
     CROSS
      JOIN
         formulas f;

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This should put you on the right track:

    Code mysql:
    DROP PROCEDURE IF EXISTS `pricing`;
    DELIMITER $$
     
    CREATE PROCEDURE `pricing`()
    BEGIN
     
            DECLARE f VARCHAR(255);
            DECLARE no_more_formulas INT DEFAULT 0;
     
            DECLARE formula_csr CURSOR FOR SELECT formula FROM formulas;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_formulas = 1;
     
            OPEN formula_csr;
            REPEAT
                    FETCH formula_csr INTO f;
                    SET @sql = CONCAT("INSERT INTO results (result) VALUES (", f, ")");
                    PREPARE s FROM @sql;
                    EXECUTE s;
                    DEALLOCATE PREPARE s;
            UNTIL no_more_formulas
            END REPEAT;
     
            CLOSE formula_csr;
     
    END$$
    DELIMITER ;

    Code:
    mysql> select * from formulas;
    +-------------+
    | formula     |
    +-------------+
    | 20.00+20.00 |
    | 100.00*2.00 |
    +-------------+
    2 rows in set (0.03 sec)
    
    mysql> call pricing;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> select * from results;
    +--------+
    | result |
    +--------+
    |     40 |
    |    200 |
    |    200 |
    +--------+
    3 rows in set (0.03 sec)
    Dunno what's up with the duplicate, but my bugs are beside the point, that you can "eval" a stored query like this.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Can something "like" this be done? I seem to be receiving an error stating that the function EXEC doesn't exists? Is EXEC not supported in functions?

    Code SQL:
    CREATE FUNCTION apply_string_pricing_calc (price DECIMAL(20,2),equation VARCHAR(96)) 
    RETURNS DECIMAL(20,2)
    RETURN EXEC(REPLACE(equation,'?',price));

    Appreciate the help.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    DELIMITER //
    CREATE PROCEDURE apply_string_pricing_calc (IN price DECIMAL(20,2),IN equation VARCHAR(96))
    BEGIN
    SET @SQL := CONCAT('SELECT ',REPLACE(equation,'?',price));
    PREPARE s FROM @SQL;
    EXECUTE s;
    END//
    DELIMITER ;

    Code SQL:
    CALL apply_string_pricing_calc('40.00','?*4');

    Produces: 160.00 which is perfect. Now just how to figure out to use it inside a query…


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •