Solved

I have MySQL Proc that does not work yet it's code outside the structure does!?

Posted on 2016-08-17
14
104 Views
Last Modified: 2016-08-24
I'm trying to populate a 'census' table with info about imports.  A couple fields I'm looking for are the int'l rate plan code and an indicator if the current plan is the best (=0), needs to downgrade (-1) or upgrade (+1).

The code words fine outside as a batch job just by changing some DELCAREd vars to local vars.
When inside it fails to update the 'census' table.

I did discover that a two table update (of one table) will certainly fail if you JOIN using USING().  It requires an ON expression.  But that's beside the point / not my issue now.

Code of PROC and same code below it outside.  The latter variant works, does update `zModem_Config`.
DROP PROCEDURE `Census_BestPlanCalc` ;


DELIMITER &&
CREATE PROCEDURE `Census_BestPlanCalc`()
BEGIN
  
  DECLARE var_latest_inv                               INTEGER UNSIGNED ;
  DECLARE posting_period                               VARCHAR(50)      ;
  
  # ===================================================================================
  
  DROP TABLE IF EXISTS `Modem_Usages` ;             # Temp table
  DROP TABLE IF EXISTS `Calcd_Charge` ;             # Temp table
  DROP TABLE IF EXISTS `Enable_Best_Calc` ;         # Temp table
  DROP TABLE IF EXISTS `CurrentBest_Comparison` ;
  
  # --------------------------------------
  SELECT invoice_id, posting_period INTO var_latest_inv, posting_period
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );
  
  # --------------------------------------
  CREATE TABLE `Modem_Usages` AS
  SELECT modem_config_id
       , intl_rate_plan_id
       , intl_data_usage
  FROM   ModemConfig mc
  JOIN   InvoicedModem im
  USING  (wireless_no)
  WHERE  im.invoice_id = var_latest_inv ;
  
  # --------------------------------------
  CREATE TABLE `Calcd_Charge` AS
  SELECT mu.modem_config_id
       , mu.intl_rate_plan_id
       , mu.intl_data_usage
       , irp.intlplan_code
       , irp.intlplan_limit_MB AS `current_threshold`
       , CASE WHEN mu.intl_data_usage/1000 <= intlplan_limit_MB THEN intlplan_rate_charge 
                                                        ELSE intlplan_rate_charge + (intl_data_usage/1000 - intlplan_limit_MB) * intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM IntlRatePlan   irp
  JOIN `Modem_Usages` mu
  USING (intl_rate_plan_id)  ;
  
  # --------------------------------------
  
  CREATE TABLE `Enable_Best_Calc` AS
  SELECT  mu.modem_config_id
       ,  mu.intl_rate_plan_id
       , irp.intlplan_code
       ,   i.intlplan_limit_MB  AS 'OtherMBs'
       ,   i.intlplan_code      AS 'AvailPlans'
       ,  CASE WHEN mu.intl_data_usage/1000 <= i.intlplan_limit_MB THEN i.intlplan_rate_charge 
                                                        ELSE i.intlplan_rate_charge + (mu.intl_data_usage/1000 - i.intlplan_limit_MB) * i.intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM   `Modem_Usages`  mu
  JOIN   `IntlRatePlan` irp
  USING  (intl_rate_plan_id)
  JOIN   `IntlRatePlan` i
  WHERE  (i.intlplan_limit_MB <> irp.intlplan_limit_MB AND SUBSTRING(i.intlplan_code,1,4) = SUBSTRING(irp.intlplan_code,1,4)
    OR    i.intlplan_limit_MB =  irp.intlplan_limit_MB AND i.intlplan_code = irp.intlplan_code)
   AND   i.date_no_more_irp_contracts > CURRENT_DATE() 
   AND   i.intlplan_code NOT IN ('Unassigned', 'Suspended', 'NoRatePlan')
   ORDER BY 1, 4;
  
  # --------------------------------------

  
  CREATE TABLE `CurrentBest_Comparison` AS
  SELECT  a.modem_config_id
        , ROUND(a.calcd_charge) AS current_charge
        , a.OtherMBs            AS current_limit
        , b.LowestCharge
        , c.AvailPlans        AS preferred_plan_code
        , c.OtherMBs          AS preferred_limit
        , SIGN(CAST(c.OtherMBs AS  SIGNED) - CAST(a.OtherMBs AS  SIGNED)) as preferred_plan_tristate
  FROM    `Enable_Best_Calc` a
  JOIN    (SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1) b
    ON    a.modem_config_id = b.modem_config_id 
  JOIN    `Enable_Best_Calc` c
    ON    a.modem_config_id = c.modem_config_id  AND ROUND(c.calcd_charge,4) = ROUND(b.LowestCharge,4)
  WHERE   a.intlplan_code = a.AvailPlans ;
  
  # ======================================
  
  UPDATE `zModem_Census`          a
  JOIN   `CurrentBest_Comparison` b
    ON   a.modem_config_id = b.modem_config_id
  SET    a.preferred_plan_code     = b.preferred_plan_code
       , a.preferred_plan_tristate = b.preferred_plan_tristate
  WHERE  a.posting_period = posting_period ;
  
  COMMIT ;
  
  
  DROP TABLE IF EXISTS `Modem_Usages` ;             # Temp table
  DROP TABLE IF EXISTS `Calcd_Charge` ;             # Temp table
  DROP TABLE IF EXISTS `Enable_Best_Calc` ;         # Temp table
  #DROP TABLE IF EXISTS `CurrentBest_Comparison` ;   # Temp table
  
END&&

DELIMITER ;

select * from `CurrentBest_Comparison` ;

  DROP TABLE IF EXISTS `Modem_Usages` ;             # Temp table
  DROP TABLE IF EXISTS `Calcd_Charge` ;             # Temp table
  DROP TABLE IF EXISTS `Enable_Best_Calc` ;         # Temp table
  DROP TABLE IF EXISTS `CurrentBest_Comparison` ;
  
  # --------------------------------------
  SELECT invoice_id, posting_period INTO @var_latest_inv, @posting_period
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );
  
  # --------------------------------------
  CREATE TABLE `Modem_Usages` AS
  SELECT modem_config_id
       , intl_rate_plan_id
       , intl_data_usage
  FROM   ModemConfig mc
  JOIN   InvoicedModem im
  USING  (wireless_no)
  WHERE  im.invoice_id = @var_latest_inv ;
  
  # --------------------------------------
  CREATE TABLE `Calcd_Charge` AS
  SELECT mu.modem_config_id
       , mu.intl_rate_plan_id
       , mu.intl_data_usage
       , irp.intlplan_code
       , irp.intlplan_limit_MB AS `current_threshold`
       , CASE WHEN mu.intl_data_usage/1000 <= intlplan_limit_MB THEN intlplan_rate_charge 
                                                        ELSE intlplan_rate_charge + (intl_data_usage/1000 - intlplan_limit_MB) * intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM IntlRatePlan   irp
  JOIN `Modem_Usages` mu
  USING (intl_rate_plan_id)  ;
  
  # --------------------------------------
  
  CREATE TABLE `Enable_Best_Calc` AS
  SELECT  mu.modem_config_id
       ,  mu.intl_rate_plan_id
       , irp.intlplan_code
       ,   i.intlplan_limit_MB  AS 'OtherMBs'
       ,   i.intlplan_code      AS 'AvailPlans'
       ,  CASE WHEN mu.intl_data_usage/1000 <= i.intlplan_limit_MB THEN i.intlplan_rate_charge 
                                                        ELSE i.intlplan_rate_charge + (mu.intl_data_usage/1000 - i.intlplan_limit_MB) * i.intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM   `Modem_Usages`  mu
  JOIN   `IntlRatePlan` irp
  USING  (intl_rate_plan_id)
  JOIN   `IntlRatePlan` i
  WHERE  (i.intlplan_limit_MB <> irp.intlplan_limit_MB AND SUBSTRING(i.intlplan_code,1,4) = SUBSTRING(irp.intlplan_code,1,4)
    OR    i.intlplan_limit_MB =  irp.intlplan_limit_MB AND i.intlplan_code = irp.intlplan_code)
   AND   i.date_no_more_irp_contracts > CURRENT_DATE() 
   AND   i.intlplan_code NOT IN ('Unassigned', 'Suspended', 'NoRatePlan')
   ORDER BY 1, 4;
  
  # --------------------------------------

  
  CREATE TABLE `CurrentBest_Comparison` AS
  SELECT  a.modem_config_id
        , ROUND(a.calcd_charge) AS current_charge
        , a.OtherMBs            AS current_limit
        , b.LowestCharge
        , c.AvailPlans        AS preferred_plan_code
        , c.OtherMBs          AS preferred_limit
        , SIGN(CAST(c.OtherMBs AS  SIGNED) - CAST(a.OtherMBs AS  SIGNED)) as preferred_plan_tristate
  FROM    `Enable_Best_Calc` a
  JOIN    (SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1) b
    ON    a.modem_config_id = b.modem_config_id 
  JOIN    `Enable_Best_Calc` c
    ON    a.modem_config_id = c.modem_config_id  AND ROUND(c.calcd_charge,4) = ROUND(b.LowestCharge,4)
  WHERE   a.intlplan_code = a.AvailPlans ;
  
  # ======================================
  
  UPDATE `zModem_Census`          a
  JOIN   `CurrentBest_Comparison` b
    ON   a.modem_config_id = b.modem_config_id
  SET    a.preferred_plan_code     = b.preferred_plan_code
       , a.preferred_plan_tristate = b.preferred_plan_tristate
  WHERE  a.posting_period = @posting_period ;
  
  COMMIT ;

Open in new window

Any suggestions?

Thanks!
0
Comment
Question by:Ralph
  • 8
  • 5
14 Comments
 

Author Comment

by:Ralph
Comment Utility
A little more info:
CREATE TABLE `zModem_Census` (
  `modem_census_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_of_census` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `posting_period` varchar(50) DEFAULT NULL,
  `modem_config_id` int(10) unsigned NOT NULL,
  `grandfathered_into_system` tinyint(3) unsigned DEFAULT '0',
  `upon_import` tinyint(3) unsigned DEFAULT '0',
  `have_all_manuf_fields` tinyint(3) unsigned DEFAULT '0',
  `have_all_shipping_fields` tinyint(3) unsigned DEFAULT '0',
  `have_billable_status_set` tinyint(3) unsigned DEFAULT '0',
  `have_utilization_set` tinyint(3) unsigned DEFAULT '0',
  `have_plan_rates_set` tinyint(3) unsigned DEFAULT '0',
  `intlplancode_before_invoice` varchar(50) DEFAULT NULL,
  `intlplancode_after_invoice` varchar(50) DEFAULT NULL,
  `domplancode_before_invoice` varchar(50) DEFAULT NULL,
  `domplancode_after_invoice` varchar(50) DEFAULT NULL,
  `preferred_plan_code` varchar(50) DEFAULT NULL,
  `preferred_plan_tristate` tinyint(4) DEFAULT '0',
  `import_changed_manuf_data` tinyint(3) unsigned DEFAULT '0',
  `import_changed_shipping_data` tinyint(3) unsigned DEFAULT '0',
  `import_changed_plans_data` tinyint(3) unsigned DEFAULT '0',
  `import_changed_billing_status` tinyint(3) unsigned DEFAULT '0',
  `have_visited_since_change` tinyint(3) unsigned DEFAULT '0',
  `posting_period_of_first_invoice` varchar(50) DEFAULT '1',
  `posting_period_of_last_invoice` varchar(50) DEFAULT '1',
  PRIMARY KEY (`modem_census_id`),
  KEY `fk_ModemCensus_ModemConfig` (`modem_config_id`),
  CONSTRAINT `fk_ModemCensus_ModemConfig` FOREIGN KEY (`modem_config_id`) REFERENCES `ModemConfig` (`modem_config_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=51780 DEFAULT CHARSET=latin1 COMMENT='Workflow or trigger populate metrics';

Open in new window

The pen-ultimate table.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Just for curiosity: Where do you use ZModem these days?
0
 

Author Comment

by:Ralph
Comment Utility
Ahhh, this is not to be confused with the Zmodem protocol!
This provides post-import metrics on modem billing plans.

At home I was hoping I'd just left off some commits in the Proc, but alas, they are there for the update.
This is BAFFLING me.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
hmmm. you are CREATING the procedure, but are you also EXECUTING it?
I see only the CREATE part ...
0
 

Author Comment

by:Ralph
Comment Utility
Hi Guy,

Ah, I don't know what you expect to see.
I call it and zModem_Census is not updated.

Why would it work as a batch job but not in a Proc?

Ralph
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
how do you exactly execute the procedure?
if your batch job is only creating the procedure, it will do that.
you should create it once, and run it with call procname
http://dev.mysql.com/doc/refman/5.7/en/call.html
0
 

Author Comment

by:Ralph
Comment Utility
At the end of PROC ImportCellModemCharges_ATnT I have:
# _____________________________________________________
  IF take_notice THEN
    INSERT INTO Import_Details (`identifier`, `level`, `thing`, `what`) VALUES (param_invoice_identifier, 'High', 'CALLing Census_GenerateMetrics()', 'Census_GenerateMetrics() calls Census_BestPlanCalc() and Census_HaveFieldsSet().' ) ;
    COMMIT ;
    IF debug THEN SELECT 'CALLing Census_GenerateMetrics(posting_period), which calls Census_BestPlanCalc() and Census_HaveFieldsSet().', SUBSTRING(param_invoice_identifier,1,8) AS 'posting_period' ; END IF ;
  END IF ; # ___________________________________________________________________________#
  
  
  CALL Generate_Metrics(SUBSTRING(param_invoice_identifier,1,8))  ;
  

Open in new window

Inside Generate_Metrics() I have:
CREATE PROCEDURE Generate_Metrics(IN posting_period1 VARCHAR(50))
BEGIN
  
  DECLARE can_up_plan_now, can_dn_plan_now, nbr_modems INTEGER  ;
  DECLARE posting_period2, posting_period3             VARCHAR(50) ;
  DECLARE var_date_of_census1, var_date_of_census2, var_date_of_census3   TIMESTAMP ;
  DECLARE modems_upgradeable1, modems_upgradeable2, modems_upgradeable3   INTEGER  ;
  DECLARE visited_up1, visited_up2, visited_up3                           INTEGER  ;
  DECLARE modems_dngradeable1, modems_dngradeable2, modems_dngradeable3   INTEGER  ;
  DECLARE visited_dn1, visited_dn2, visited_dn3                           INTEGER  ;
  
  
  # =========================================================================
  
  DROP TABLE IF EXISTS census_up_grade_1mo ;  # Temp table
  DROP TABLE IF EXISTS census_up_grade_2mo ;  # Temp table
  DROP TABLE IF EXISTS census_up_grade_3mo ;  # Temp table
  DROP TABLE IF EXISTS census_dn_grade_1mo ;  # Temp table
  DROP TABLE IF EXISTS census_dn_grade_2mo ;  # Temp table
  DROP TABLE IF EXISTS census_dn_grade_3mo ;  # Temp table
  DROP TABLE IF EXISTS yUpgradeable_3mo ;
  DROP TABLE IF EXISTS yDowngradeable_3mo ;
  DROP TABLE IF EXISTS yImport_Changes ;
  DROP TABLE IF EXISTS yMissing_Fields ;
  DROP TABLE IF EXISTS yMissing_Plan_Data ;
  
  # =========================================================================
  # This call to Cencus_BestPlanCalc MUST come before Census_HaveFieldsSet
  
  CALL Census_BestPlanCalc()  ;
  
  CALL Census_HaveFieldsSet() ;
  ...
...

Open in new window

The code runs, as the table `CurrentBest_Comparison`gets created every time I do an import of charges.
I also run it manually with a CALL to the procedure, and still not change to `zModem_Census`; UNLESSI run just the code with 'local' variables outside of the PROC.
SELECT invoice_id, posting_period INTO @var_latest_inv, @var_posting_period
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );

Open in new window

Odd...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
seems so far all nice.
so, the only explanation that I would go for is that still there is something going on like "wrong database selected "...
from what you explain, the code is correct, and you execute all correctly.
if this last short into the blue is not the issue, I would try to get some other pair of eyes locally on your server (gigs?) to find out the issue
0
 

Author Comment

by:Ralph
Comment Utility
I was thinking I might have to look into the explain plan. To find out what explain plan was used in a procedure though, I don't know how to do that.
Something new to find out.
0
 

Author Comment

by:Ralph
Comment Utility
Nope, can't EXPLAIN an UPDATE command.  Also, apologies for "it's" rather than "its" in the title.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the explain plan (if possible) would only tell you how the query was executed, but not inside which db or server...
0
 

Author Comment

by:Ralph
Comment Utility
Unfortunately always same DB same server.  That's certain.

-- -- -- -- --
More Info:

Seems the UPDATE is flaky, it won't always work outside of the PROC either.

18:26:34      UPDATE  `zModem_Census`          a        ,  `CurrentBest_Comparison` b   SET     a.preferred_plan_code     = b.preferred_plan_code        ,  a.preferred_plan_tristate = b.preferred_plan_tristate   WHERE   a.posting_period  = 'Tue Aug 23 2016 @ 18:22:50'     AND   a.modem_config_id = b.modem_config_id      
0 row(s) affected Rows matched: 1566  Changed: 0  Warnings: 0      0.187 sec

When it does work... see preferred_plan_code and preferred_plan_tristate.
This is from the zModem_Census table.
When it does work
In my second post on this issue on 2016-08-17 at 18:31:04ID: 41760438, the image there is of the last working table before the update.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
>0 row(s) affected Rows matched: 1566  Changed: 0  Warnings: 0      0.187 sec
this message tells you can if finds 1566 rows that would be updated, but 0 rows that actually get a value being CHANGED
at least, that is what I interprete into some posts around.

which means that maybe you have already the same data in both tables for the 2 columns being updated.
can you test that using a SELECT to check if the values are really different?
1
 

Author Closing Comment

by:Ralph
Comment Utility
I Just ran it again, this time (I'm using MySQL Workbench) I 'Reconnect(ed) to server' first, so maybe it's that and maybe since I'm now not only doing this calculation upon a posting_period, (when a bill is imported), but also using other criteria, so MAYBE it's the other code changes, but [b]IT WORKED[/b]!

I really don't like these 'now you see it, now you don't' scenarios.
Nor do I like MySQL Workbench using cached data rather than letting me know it lost its connection.

Thanks for bearing with me on this Guy.
And if I'm unlucky and this issue rears its head again, BEWARE!

Ciao for now,
Ralph
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now