Setup
In the Below Stored procedure what I'm trying to do is create a few Temp Tables. Some of which are simple and static, and 2 of which are dynamic (TempQuery1, TempQuery2).
All temp tables are Built -> Prepared -> executed first. Then the main query that utilizes the temp tables (GetQuery) is executed.
Problem
When executed exactly like it is on a fresh connection, I get
Error Code: 1146. Table 'Job1111.TempPipeER' doesn't exist
Which lead me to think I had a typeo or something.. In testing I found that if I follow the next couple steps t works fine.
Question
Observation/Guess
The way it is acting is like the Execute BuildGetQuery is executing before TempTable Job1111.TempPipeER is finished building. And sine GetQuery utilizes the TempPipeER able in a Join. The GetQuery fails.
But if I slowly build each table one at a time, then it works fine for the duration of that MySQL work bench session.
Stored Procedure Create Statement
DROP PROCEDURE IF EXISTS `Job1111`.`GetPipeStats`;
DELIMITER $$
CREATE DEFINER=`PSAAdmin`@`%` PROCEDURE `Job1111`.`GetPipeStats`( IN GroupedBy varchar(24))
BEGIN
### Declare Variables ###
DECLARE MechHoursPercent DECIMAL(3,2);
DECLARE TestingHoursPercent DECIMAL(3,2);
DECLARE CompleteForTestPercent DECIMAL(3,2);
DECLARE HydroHoursPercent DECIMAL(3,2);
DECLARE ClientTurnoverPercent DECIMAL(3,2);
### Set Variables ###
SELECT Value1 INTO MechHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "MechHoursPercent";
SELECT Value1 INTO TestingHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "TestingHoursPercent";
SELECT Value1 INTO CompleteForTestPercent
FROM `Job1111`.SETTINGS WHERE Label = "CompleteForTestPercent";
SELECT Value1 INTO HydroHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "HydroHoursPercent";
SELECT Value1 INTO ClientTurnoverPercent
FROM `Job1111`.SETTINGS WHERE Label = "ClientTurnoverPercent";
### Build Temp Table Queries ###
#DROP TABLE IF EXISTS `Job1111`.`TempPipeER`, `Job1111`.`TempPipeERT`;
SET @TempQuery1 = CONCAT('
CREATE TEMPORARY TABLE `Job1111`.`TempPipeER`(
INDEX `TempPipeER1` (`ID` ASC)
)
SELECT ID, BidID,
sum(EarnedErectionHours) AS EarnedErectionHours,
sum(EarnedBoltUpHours) AS EarnedBoltUpHours,
sum(EarnedWeldHours) AS EarnedWeldHours,
sum(EarnedCutHours) AS EarnedCutHours,
sum(EarnedTrimHours) AS EarnedTrimHours,
sum(EarnedSupportHours) AS EarnedSupportHours,
sum(EarnedDemoHours) AS EarnedDemoHours,
sum(EarnedMiscHours) AS EarnedMiscHours,
sum(InstalledFootage) AS InstalledFootage,
sum(InstalledBoltUps) AS InstalledBoltUps,
sum(InstalledWelds) AS InstalledWelds,
sum(InstalledCuts) AS InstalledCuts,
sum(InstalledTrim) AS InstalledTrim,
sum(InstalledSupports) AS InstalledSupports,
sum(InstalledMisc) AS InstalledMisc,
sum(InstalledDemo) AS InstalledDemo
FROM(SELECT min(td.ID) AS ID,td.BidID,
IF(
COALESCE(se.ErectionLengthSpool1,0)
+ COALESCE(se.ErectionLengthSpool2,0)
+ COALESCE(se.ErectionLengthSpool3,0)
+ COALESCE(se.ErectionLengthSpool4,0)
+ COALESCE(se.ErectionLengthSpool5,0)
+ COALESCE(se.ErectionLengthSpool6,0)
+ COALESCE(sme.MiscQuantity,0)
+ sum(COALESCE(cse.InstalledQuantity,0)) > td.TakeOffQuantity, /*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cse.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/
COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity)
* (COALESCE(se.ErectionLengthSpool1,0)
+ COALESCE(se.ErectionLengthSpool2,0)
+ COALESCE(se.ErectionLengthSpool3,0)
+ COALESCE(se.ErectionLengthSpool4,0)
+ COALESCE(se.ErectionLengthSpool5,0)
+ COALESCE(se.ErectionLengthSpool6,0)
+ COALESCE(sme.MiscQuantity,0)),0)
) AS EarnedErectionHours,
IF(COALESCE(sb.BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0) + sum(COALESCE(csb.InstalledQuantity,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csb.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(sb.BoltUpQuantity,0)+COALESCE(smb.MiscQuantity,0)), 0)
) AS EarnedBoltUpHours,
IF(COALESCE(sw.WeldPercentCompleted * .01,0) + COALESCE(smw.MiscQuantity,0) + sum(COALESCE(csw.InstalledQuantity * .01,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csw.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(sw.WeldPercentCompleted * .01,0)+COALESCE(smw.MiscQuantity,0)),0)
) AS EarnedWeldHours,
IF(COALESCE(scut.WeldPercentCompleted * .01,0) + COALESCE(smc.MiscQuantity,0) + sum(COALESCE(csc.InstalledQuantity * .01,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csc.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(scut.WeldPercentCompleted * .01,0)+COALESCE(smc.MiscQuantity,0)),0)
) AS EarnedCutHours,
IF(COALESCE(st.TrimQuantity,0) + COALESCE(smt.MiscQuantity,0) + sum(COALESCE(cst.InstalledQuantity,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cst.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(st.TrimQuantity,0)+COALESCE(smt.MiscQuantity,0)),0)
) AS EarnedTrimHours,
IF(COALESCE(ssu.SupportInstalled,0) + COALESCE(smssu.MiscQuantity,0) + sum(COALESCE(cssu.InstalledQuantity,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cssu.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity)*(COALESCE(ssu.SupportInstalled,0)+COALESCE(smssu.MiscQuantity,0)),0)
) AS EarnedSupportHours,
IF(COALESCE(sde.DemoQuantity,0) + COALESCE(smd.MiscQuantity,0) + sum(COALESCE(csd.InstalledQuantity,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csd.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours/td.TakeOffQuantity) * (COALESCE(sde.DemoQuantity,0)+COALESCE(smd.MiscQuantity,0)),0)
) AS EarnedDemoHours,
IF(COALESCE(sm.MiscQuantity,0) + sum(COALESCE(csm.InstalledQuantity,0))
> td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csm.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * COALESCE(sm.MiscQuantity,0),0)
) AS EarnedMiscHours,
sum(COALESCE(se.ErectionLengthSpool1,0) + COALESCE(se.ErectionLengthSpool2,0) + COALESCE(se.ErectionLengthSpool3,0) + COALESCE(se.ErectionLengthSpool4,0) + COALESCE(se.ErectionLengthSpool5,0) + COALESCE(se.ErectionLengthSpool6,0) + COALESCE(sme.MiscQuantity,0)) AS InstalledFootage,
sum(COALESCE(sb.BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0)) AS InstalledBoltUps,
sum(COALESCE(sw.WeldPercentCompleted * .01,0) + COALESCE(smw.MiscQuantity,0)) AS InstalledWelds,
sum(COALESCE(scut.WeldPercentCompleted * .01,0) + COALESCE(smc.MiscQuantity,0)) AS InstalledCuts,
sum(COALESCE(st.TrimQuantity,0) + COALESCE(smt.MiscQuantity,0)) AS InstalledTrim,
sum(COALESCE(ssu.SupportInstalled,0) + COALESCE(smssu.MiscQuantity,0)) AS InstalledSupports,
sum(COALESCE(sm.MiscQuantity,0)) AS InstalledMisc,
sum(COALESCE(sde.DemoQuantity,0) + COALESCE(smd.MiscQuantity,0)) AS InstalledDemo
FROM `Job1111`.SubmissionStatus ss
JOIN PSA.REF_ActivityTypes at
ON at.ID = ss.ActivityType
LEFT JOIN `Job1111`.SubsErection se
ON se.ID = ss.ActivityRowId AND ss.ActivityType = 11 AND se.ErectionRework = 0
LEFT JOIN `Job1111`.SubsBoltUp sb
ON sb.ID = ss.ActivityRowId AND ss.ActivityType = 21 AND sb.BoltUpRework = 0
LEFT JOIN `Job1111`.SubsWeld sw
ON sw.ID = ss.ActivityRowId AND ss.ActivityType = 22 AND sw.WeldRework = 0
LEFT JOIN `Job1111`.SubsWeld scut
ON scut.ID = ss.ActivityRowId AND ss.ActivityType = 23 AND scut.WeldRework = 0
LEFT JOIN `Job1111`.SubsTrim st
ON st.ID = ss.ActivityRowId AND ss.ActivityType IN (30,31,33,34,35,39) AND st.TrimRework = 0
LEFT JOIN (SELECT ssu.ID, COALESCE((ssu.SupportPercentCompleted * .01 * ssu.SupportQuantity),0) AS SupportInstalled
FROM `Job1111`.SubsSupports ssu
WHERE ssu.SupportRework = 0) ssu
ON ssu.ID = ss.ActivityRowId AND ss.ActivityType = 32
LEFT JOIN `Job1111`.SubsDemo sde
ON sde.ID = ss.ActivityRowId AND ss.ActivityType IN (50,51,52,53,59)
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
ON td.BidID = ss.fk_BidIDAssigned
/* #### Begining of Misc activities assigned joins #### */
LEFT JOIN `Job1111`.SubsMisc sm
ON sm.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "MISC" AND sm.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc sme
ON sme.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "PE" AND sme.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc smb
ON smb.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "BU" AND smb.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc smw
ON smw.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "W" AND smw.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc smc
ON smc.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "CUT" AND smc.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc smt
ON smt.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "T" AND td.PipingActivityType IN ("V","Plug","HpLp","Other","Blind") AND smt.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc smssu
ON smssu.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "S" AND smssu.MiscRework = 0
LEFT JOIN `Job1111`.SubsMisc smd
ON smd.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "D" AND smd.MiscRework = 0
/* #### Begining of Cumulative Past sums Calculation #### */
LEFT JOIN `Job1111`.`TempPipeCSE` cse
ON ss.ActivityType IN (11,91) AND ss.fk_BidIDAssigned = cse.fk_BidIDAssigned AND cse.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCSB` csb
ON ss.ActivityType IN (21,91) AND ss.fk_BidIDAssigned = csb.fk_BidIDAssigned AND csb.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCSW` csw
ON ss.ActivityType IN (22,91) AND ss.fk_BidIDAssigned = csw.fk_BidIDAssigned AND csw.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCSC` csc
ON ss.ActivityType IN (23,91) AND ss.fk_BidIDAssigned = csc.fk_BidIDAssigned AND csc.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCST` cst
ON ss.ActivityType IN (30,31,33,34,35,39,91) AND ss.fk_BidIDAssigned = cst.fk_BidIDAssigned AND cst.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCSSU` cssu
ON ss.ActivityType IN (32,91) AND ss.fk_BidIDAssigned = cssu.fk_BidIDAssigned AND cssu.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCSD` csd
ON ss.ActivityType IN (50,51,52,53,59,91) AND ss.fk_BidIDAssigned = csd.fk_BidIDAssigned AND csd.PSAActivityID < ss.PSAActivityID
LEFT JOIN `Job1111`.`TempPipeCSM` csm
ON ss.ActivityType = 91 AND ss.fk_BidIDAssigned = csm.fk_BidIDAssigned AND csm.PSAActivityID < ss.PSAActivityID
WHERE fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (10,11,20,21,22,23,30,31,32,33,34,35,39,50,51,52,53,59,90,91,93)
GROUP BY ss.PSAActivityID)er0
GROUP BY BidID
');
SET @TempQuery2 = CONCAT('
CREATE TEMPORARY TABLE `Job1111`.`TempPipeERT`(
INDEX `TempPipeERT1` (`ID` ASC)
)
SELECT min(td.ID) AS ID, td.BidID,
IF(
(COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ' * sum(COALESCE(steR.InstalledReadyForTesting, 0)))
> COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ',
/*TRUE*/
COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ',
/*FALSE*/
(COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ' * sum(COALESCE(steR.InstalledReadyForTesting, 0)))
) AS EarnedReadyForTestingHours,
IF(
(COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ' * sum(COALESCE(steH.InstalledHydro, 0)))
> COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ',
/*TRUE*/
COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ',
/*FALSE*/
(COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ' * sum(COALESCE(steH.InstalledHydro, 0)))
) AS EarnedHydroHours,
IF(
(COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ' * sum(COALESCE(steC.InstalledClientTurnover, 0)))
> COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ',
/*TRUE*/
COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ',
/*FALSE*/
(COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ' * sum(COALESCE(steC.InstalledClientTurnover, 0)))
) AS EarnedClientTurnoverHours
FROM `Job1111`.trackerDetails td
LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledReadyForTesting
FROM `Job1111`.SubsTesting ste
JOIN `Job1111`.SubmissionStatus ss
ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 41 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steR
ON steR.TestingTestPackageNumber = td.TestPackageNumber AND steR.ActivityType = 41 AND td.ECT != "D"
LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledHydro
FROM `Job1111`.SubsTesting ste
JOIN `Job1111`.SubmissionStatus ss
ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 42 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steH
ON steH.TestingTestPackageNumber = td.TestPackageNumber AND steH.ActivityType = 42 AND td.ECT != "D"
LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledClientTurnover
FROM `Job1111`.SubsTesting ste
JOIN `Job1111`.SubmissionStatus ss
ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 43 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steC
ON steC.TestingTestPackageNumber = td.TestPackageNumber AND steC.ActivityType = 43 AND td.ECT != "D"
GROUP BY td.BidID
');
### Build Main Query ###
SET @GetQuery = CONCAT('
QUERY REMOVED DUE TO BODY SIZE LIMIT
');
### Execute NON Prep Queries ###
DROP TABLE IF EXISTS `Job1111`.`TempTrackerDetailsPipe`;
CREATE TEMPORARY TABLE `Job1111`.`TempTrackerDetailsPipe`(
INDEX `TempPipeTD1` (`BidID` ASC),
INDEX `TempPipeTD2` (`PipingActivityType` ASC, `ECT` ASC),
INDEX `TempPipeTD3` (`ECT` ASC)
)
SELECT min(ID) AS ID, BidID, sum(TakeOffQuantity) AS TakeOffQuantity, sum(EstimatedHours) AS EstimatedHours, ECT, PipingActivityType
FROM`Job1111`.`trackerDetails`
GROUP BY BidID;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSE`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSE`(
INDEX `TempPipeCSE1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(ErectionLengthSpool1,0) + COALESCE(ErectionLengthSpool2,0) + COALESCE(ErectionLengthSpool3,0) + COALESCE(ErectionLengthSpool4,0) + COALESCE(ErectionLengthSpool5,0) + COALESCE(ErectionLengthSpool6,0) + COALESCE(sme.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsErection
ON ss.ActivityType = 11 AND ss.ActivityRowId = ID AND ErectionRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc sme
ON sme.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "PE" AND sme.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (11,91)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSB`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSB`(
INDEX `TempPipeCSB1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsBoltUp
ON ss.ActivityType = 21 AND ss.ActivityRowId = ID AND BoltUpRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smb
ON smb.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "BU" AND smb.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (21,91)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSW`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSW`(
INDEX `TempPipeCSW1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(WeldPercentCompleted,0) + COALESCE(smw.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsWeld
ON ss.ActivityType = 22 AND ss.ActivityRowId = ID AND WeldRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smw
ON smw.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "W"AND smw.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (22,91)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSC`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSC`(
INDEX `TempPipeCSC1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(WeldPercentCompleted,0) + COALESCE(smc.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsWeld
ON ss.ActivityType = 23 AND ss.ActivityRowId = ID AND WeldRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smc
ON smc.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "CUT" AND smc.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (23,91)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCST`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCST`(
INDEX `TempPipeCST1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(TrimQuantity,0) + COALESCE(smt.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsTrim
ON ss.ActivityType IN (30,31,33,34,35,39) AND ss.ActivityRowId = ID AND TrimRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smt
ON smt.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "T" AND td.PipingActivityType IN ("V","Plug","HpLp","Other","Blind")AND smt.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (30,31,33,34,35,39,91)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSSU`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSSU`(
INDEX `TempPipeCSSU1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(SupportQuantity,0) + COALESCE(smssu.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsSupports
ON ss.ActivityType = 32 AND ss.ActivityRowId = ID AND SupportRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smssu
ON smssu.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "S" AND smssu.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSD`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSD`(
INDEX `TempPipeCSD` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(DemoQuantity,0) + COALESCE(smd.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsDemo
ON ss.ActivityType IN (50,51,52,53,59) AND ss.ActivityRowId = ID
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smd
ON smd.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "D" AND smd.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (50,51,52,53,59,91)
HAVING InstalledQuantity > 0;
DROP TABLE IF EXISTS `Job1111`.`TempPipeCSM`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSM`(
INDEX `TempPipeCSM` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, MiscQuantity AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
ON td.BidID = ss.fk_BidIDAssigned
JOIN `Job1111`.SubsMisc sm
ON ss.ActivityType = 91 AND ss.ActivityRowId = sm.ID AND td.PipingActivityType = "MISC" AND td.ECT != "D" AND sm.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12);
### Prepare Queries to execute ###
PREPARE BuildTempQuery1 FROM @TempQuery1;
PREPARE BuildTempQuery2 FROM @TempQuery2;
PREPARE BuildGetQuery FROM @GetQuery;
### Execute Prep Queries ###
EXECUTE BuildTempQuery1;
EXECUTE BuildTempQuery2;
EXECUTE BuildGetQuery;
### Deallocate Execute Querys and Tables###
DEALLOCATE PREPARE BuildTempQuery1;
DEALLOCATE PREPARE BuildTempQuery2;
DEALLOCATE PREPARE BuildGetQuery;
END$$
DELIMITER ;
The stored procedure is perfectly sound. Except for one item. Due to the fact that some prepared statements use Temp tables made by other prepared statements. Care needs to be taken to Prepare AND EXECUTE the statements in the right order.
Change This
### Prepare Queries to execute ###
PREPARE BuildTempQuery1 FROM @TempQuery1;
PREPARE BuildTempQuery2 FROM @TempQuery2;
PREPARE BuildGetQuery FROM @GetQuery;
### Execute Prep Queries ###
EXECUTE BuildTempQuery1;
EXECUTE BuildTempQuery2;
EXECUTE BuildGetQuery;
### Deallocate Execute Querys and Tables###
DEALLOCATE PREPARE BuildTempQuery1;
DEALLOCATE PREPARE BuildTempQuery2;
DEALLOCATE PREPARE BuildGetQuery;
To This
### Prepare Pass 1 Queries to execute ###
PREPARE BuildTempQuery1 FROM @TempQuery1;
PREPARE BuildTempQuery2 FROM @TempQuery2;
### Execute Pass 1 Queries ###
EXECUTE BuildTempQuery1 ;
EXECUTE BuildTempQuery2 ;
### Prepare Pass 2 Queries to execute ###
PREPARE BuildGetQuery FROM @GetQuery;
### Execute Pass 2 Queries ###
EXECUTE BuildGetQuery;
### Deallocate Execute Querys and Tables###
DEALLOCATE PREPARE BuildTempQueryER;
DEALLOCATE PREPARE BuildTempQueryERT;
DEALLOCATE PREPARE BuildGetQuery;
Explanation
The reason this is needed, is since "BuildGetQuery" is looking for the Temp Table created by "BuildTempQuery1". It must exist BEFORE PREPARE of BuildGetQuery.
The mistake was thinking the "Do these tables exist?" check performed during the execution of "BuildGetQuery" where looking for tables made in BuildTempQuery1 and BuildTempQuery1 happen at EXECUTE and not at PREPARE.
Turns out this check happens at PREPARE of "BuildGetQuery". Meaning the Temp queries need to first be executed.
The one peace I'm missing is where in the MySQL documentation this reality is explained/demonstrated.