We're having a tough time debugging because our retired predecessor has a query statement that puts pulled data into a series of temporary tables before uploading to the database. It's not working now (we added items to TestPatterns table, and running for tp7 instead of tp21 so data may differ) and we're having a tough time debugging it. If we could somehow print or access the data put in the temporary tables, maybe we could debug it better. Any ideas on how to debug this better, and maybe see the data? We can print our first select/where statement, but not the data it pulls when the qry is executed. Plus there's the Source/Target sql merges we do. We know everything seems to work until we execute the $qry1d.
I looked online for how to view this info, and see the sql profiler, but I'm worried the statements are too complex to decipher the profiler output. When I tried to profile withhttp://youtu.be/mJ8Dyv4Uk6E, for a simple select top 1000 rows, it said:
exec sp_executesql N'SELECT
clmns.name AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
WHERE
(CAST(clmns.is_sparse AS bit)=@_msparam_0)and((tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2))
ORDER BY
clmns.column_id ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'Measurements',@_msparam_2=N'dbo'
It's failing with
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This is a couple of the php sql merge statements:
$qry = 'SELECT "PrintSamples"."PrintSampleID", "PrintSamples"."TestPatternName", "PrintSamples"."PrintCopyID",
"DigitalImages"."CaptureTime", "PrintSampleAnalyses"."psaTicket", "Measurements"."MeasurementID", "Measurements"."MeasurementUuid",
SUBSTRING("OperatorLastName",1,1) AS "lastInitial", SUBSTRING("OperatorFirstName",1,1) AS "firstInitial",
"ParameterValues"."ParameterID", "ParameterName", "TargetName", "ParameterValues"."ParameterValue"
FROM "ParameterValues"
LEFT JOIN "Measurements" ON "ParameterValues"."MeasurementID"="Measurements"."MeasurementID"
LEFT JOIN "PrintSampleAnalyses" ON "PrintSampleAnalyses"."psaID"="Measurements"."psaID"
LEFT JOIN "DigitalImages" ON "DigitalImages"."ImageID"="PrintSampleAnalyses"."ImageID"
LEFT JOIN "PrintSamples" ON "DigitalImages"."PrintSampleID"="PrintSamples"."PrintSampleID"
LEFT JOIN "Sessions" ON "Sessions"."SessionID"="PrintSampleAnalyses"."SessionID"
LEFT JOIN "Operators" ON "Operators"."OperatorID"="Sessions"."OperatorID"
LEFT JOIN "ParameterNames" ON "ParameterNames"."ParameterID"="ParameterValues"."ParameterID"
LEFT JOIN "Targets" ON "Targets"."TargetID"="Measurements"."TargetID"
WHERE (';
//----------------------------------------------------------------------------------------------------
// The two statements where added to make AvgGhostValAsSir77 and MaxNegGhostingValAsSir77 work.
// After much testing it was found that the query did not recognize these to ParameterNames in the
// Postgres database. It was never discovered why when testing the ParameterName to be equal to
// AvgGhostValAsSir77 or MaxNegGhostingValAsSir77 the query fails but using the LIKE statement
// corrected the problem. It is possible that the names contain a hidden character or space that
// caused the proble. More investigation will need to be done to find a better resolution to this
// strange problem. On 8/1/13 - It was found that the two Parameters AvgGhostValAsSir77 and MaxNegGhostingValAsSir77
// have a trailing space to thier names in the Postgres database and that's why these two parameters
// were NOT working. It was decided instead of having IQAF people modify the database that the two
// statements using the LIKE will remain in place. The file was modified on this date 8/1/13.
//----------------------------------------------------------------------------------------------------
// only take values that actually get reported on the dashboard
// this list comes from the "MeasurementNames" table
foreach ($measurementIDs as $mid){
if($mid[0] == "AvgGhostValAsSir77") $qry .= '(("ParameterName" LIKE ' . "'%AvgGhostValAsSir77%'" . ') AND ("TargetName"=' . "'" . $mid[1] . "')) OR ";
else if($mid[0] == "MaxNegGhostingValAsSir77") $qry .= '(("ParameterName" LIKE ' . "'%MaxNegGhostingValAsSir77%'" . ') AND ("TargetName"=' . "'" . $mid[1] . "')) OR ";
else $qry .= '(("ParameterName"=' . "'" . $mid[0] . "'" . ') AND ("TargetName"=' . "'" . $mid[1] . "')) OR ";
}
$qry = substr ($qry, 0, -4);
$qry .= ") ";
if (isset($captureTime)){ // used for incremental updates
$qry .= ' AND ("CaptureTime">' . "'" . $captureTime . "')";
}
// steve invalid reading code is -99999.
$qry .= ' AND ("ParameterValues"."ParameterValue" != -99999) ORDER BY "PrintSampleID", "MeasurementID"';
$actionString = '$action';
$qryCreate = "CREATE TABLE #tmpMeasurementTable (TestGUID uniqueidentifier,
IQAFid uniqueidentifier, pqID int, MeasurementID int,
EvaluationDate datetime, EvaluatorID int,
TestUnitID int, TestUnitCountID int,
TestPatternID int, ColorID int,
TargetID int, ParameterID int,
ParameterValue real)
CREATE TABLE #MergeOutput (ActionType nvarchar(10))";
//start putting data into measurement tables
$qry1a= "INSERT INTO #tmpMeasurementTable VALUES ";
//put 1a data in MeasurementData
$qry1b = "
MERGE INTO MeasurementData AS Target
USING #tmpMeasurementTable AS Source
ON Target.pqID=Source.pqID
AND Target.MeasurementID=Source.MeasurementID
AND Target.TargetID=Source.TargetID
AND Target.ParameterID=Source.ParameterID
AND Target.TestPatternID=Source.TestPatternID
AND Target.IQAFMeasurementID=Source.IQAFid
WHEN MATCHED THEN
UPDATE SET Target.ParameterValue = Source.ParameterValue,
Target.TestUnitID=Source.TestUnitID,
Target.TestUnitCountID=Source.TestUnitCountID,
Target.EvaluationDate=Source.EvaluationDate,
Target.EvaluatorID=Source.EvaluatorID
WHEN NOT MATCHED BY Target THEN
INSERT (TestGUID, IQAFMeasurementID, pqID,
MeasurementID, EvaluationDate, EvaluatorID,
TestUnitID, TestUnitCountID, TestPatternID, ColorID,
TargetID, ParameterID, ParameterValue)
VALUES (Source.TestGUID, Source.IQAFid, Source.pqID,
Source.MeasurementID, Source.EvaluationDate, Source.EvaluatorID,
Source.TestUnitID, Source.TestUnitCountID,
Source.TestPatternID, Source.ColorID,
Source.TargetID, Source.ParameterID, Source.ParameterValue)
OUTPUT
$actionString INTO #MergeOutput;
DROP TABLE #tmpMeasurementTable";
$qryOutput = "SELECT ActionType, COUNT(ActionType)AS [count] FROM #MergeOutput GROUP BY ActionType";
$qryCleanup = "DROP TABLE #MergeOutput";
$qry1c = "IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MergeOutput') AND xtype='U')
DROP TABLE #MergeOutput
CREATE TABLE #MergeOutput (ActionType nvarchar(10))
SELECT TestIndex AS TestID, TestID AS TestNumber, MeasurementData.TestGUID, pqID, TestUnitID, TestUnitCountID, TestPatternID,
ColorID, MeasurementData.MeasurementID, TargetID, ParameterID,
CAST(ROUND(AVG(ParameterValue*Multiplier), 2)AS DECIMAL(18,2)) AS Value
INTO #tmpTable
FROM MeasurementData
LEFT JOIN Measurements ON Measurements.MeasurementID=MeasurementData.MeasurementID
LEFT JOIN Tests ON Tests.TestGUID=MeasurementData.TestGUID AND Tests.PiggybackID IS NULL
WHERE MeasurementData.TestGUID='" . $TestGUID . "' AND pqID>=" . $startPQid . " AND pqID<=" .$endPQid;
if (isset($captureTime)) // used for incremental updates
{
$qry1c .= " AND EvaluationDate>'" . makeDateTime($captureTime) . "'";
}
$qry1c .= " GROUP BY TestIndex, TestID, MeasurementData.TestGUID, pqID, TestUnitID, TestUnitCountID, TestPatternID,
ColorID, MeasurementData.MeasurementID, TargetID, ParameterID
ORDER BY ColorID, TestPatternID, TestUnitCountID, MeasurementData.MeasurementID, TargetID, ParameterID;
SELECT @@ROWCOUNT AS rows INTO #DashboardRows;";
//put temporary table in dashboard table
$qry1d = ";
MERGE INTO DashboardData AS Target
USING #tmpTable AS Source
ON Target.pqID=Source.pqID
AND Target.MeasurementID=Source.MeasurementID
AND Target.TargetID=Source.TargetID
AND Target.ParameterID=Source.ParameterID
AND Target.TestPatternID=Source.TestPatternID
WHEN MATCHED THEN
UPDATE SET Target.ParameterValue=Source.Value,
Target.TestUnitID=Source.TestUnitID,
Target.TestUnitCountID=Source.TestUnitCountID
WHEN NOT MATCHED BY Target THEN
INSERT (TestGUID, pqID,
MeasurementID,
TestUnitID, TestUnitCountID, TestPatternID, ColorID,
TargetID, ParameterID, ParameterValue,
TestNumber, TestIndex)
VALUES (Source.TestGUID, Source.pqID,
Source.MeasurementID,
Source.TestUnitID, Source.TestUnitCountID,
Source.TestPatternID, Source.ColorID,
Source.TargetID, Source.ParameterID, Source.Value,
Source.TestNumber, Source.TestID)
OUTPUT
$actionString INTO #MergeOutput;
DROP TABLE #tmpTable";
The queries get executed like this:
$result = $ms_conn->query($qry1c); $recordCount = $ms_conn->fetchOne
("SELECT rows FROM #DashboardRows"); //dataLog ("OLAP database " .
$recordCount . " records to process", true); $result =
$ms_conn->query($qry1d); dataLog ("end dashboard query");