Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

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

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
Ralph
Asked:
Ralph
  • 8
  • 5
1 Solution
 
RalphAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
Just for curiosity: Where do you use ZModem these days?
0
 
RalphAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmmm. you are CREATING the procedure, but are you also EXECUTING it?
I see only the CREATE part ...
0
 
RalphAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RalphAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RalphAuthor Commented:
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
 
RalphAuthor Commented:
Nope, can't EXPLAIN an UPDATE command.  Also, apologies for "it's" rather than "its" in the title.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the explain plan (if possible) would only tell you how the query was executed, but not inside which db or server...
0
 
RalphAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
RalphAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now