Changeset 15826

Timestamp:

Dec 14, 2007, 10:22:45 AM (18 years ago)

Author:

eugene

Message:

fixed sql to return the correct count of class_ids

File:

• trunk/ippTools/share/dettool_tonormalizedexp.sql (modified) (2 diffs)

trunk/ippTools/share/dettool_tonormalizedexp.sql

@@ -1 @@
-SELECT DISTINCT
-    *
-FROM
-    (SELECT DISTINCT
-        detRun.det_id as det_id,
+SELECT 
+  *
+--  COUNT(norm_class_id) as nNorm,
+--  COUNT(stack_class_id) as nStack
+FROM (
+    SELECT DISTINCT
+        detRun.det_id,
         detRun.iteration,
         detRun.det_type,
-        detRun.workdir,
+--      detRun.workdir,
+--      rawExp.exp_id,
         rawExp.camera,
         rawExp.telescope,
         rawExp.exp_type,
-        detNormalizedImfile.class_id,
-        rawImfile.class_id as rawimfile_class_id
+--      detNormalizedExp.det_id as norm_det_id,
+        detNormalizedImfile.class_id as norm_class_id,
+        detNormalizedImfile.fault,
+        detStackedImfile.class_id as stack_class_id
     FROM detRun
     JOIN detInputExp
@@ -18 +23 @@
     JOIN rawExp
         ON detInputExp.exp_id = rawExp.exp_id
-    JOIN detNormalizedImfile
-        ON detInputExp.det_id = detNormalizedImfile.det_id
-        AND detInputExp.iteration = detNormalizedImfile.iteration
-    LEFT JOIN rawImfile
-        ON detInputExp.exp_id = rawImfile.exp_id
-        AND detNormalizedImfile.class_id = rawImfile.class_id
+    JOIN detStackedImfile
+        ON detStackedImfile.det_id = detRun.det_id
+        AND detStackedImfile.iteration = detRun.iteration
+    LEFT JOIN detNormalizedImfile
+        ON detStackedImfile.det_id = detNormalizedImfile.det_id
+        AND detStackedImfile.iteration = detNormalizedImfile.iteration
+        AND detStackedImfile.class_id = detNormalizedImfile.class_id
     LEFT JOIN detNormalizedExp
         ON detInputExp.det_id = detNormalizedExp.det_id
         AND detInputExp.iteration = detNormalizedExp.iteration
     WHERE
-        detRun.state = 'run'
-        AND detRun.mode = 'master'
-        AND detNormalizedExp.det_id IS NULL
-        AND detNormalizedExp.iteration IS NULL
-        AND detInputExp.include = 1
-    GROUP BY
-        detNormalizedImfile.iteration,
-        detRun.det_id
-    HAVING
-        COUNT(detNormalizedImfile.class_id) = COUNT(rawImfile.class_id)
-        AND SUM(detNormalizedImfile.fault) = 0
-    ) as Foo
+--        detRun.state = 'run' AND
+--        detRun.mode = 'master' AND
+        detNormalizedExp.det_id IS NULL AND
+        detNormalizedExp.iteration IS NULL AND
+        detInputExp.include = 1
+) as Foo
+GROUP BY
+  det_id,
+  iteration
+HAVING
+  COUNT(norm_class_id) = COUNT(stack_class_id)
+  AND SUM(fault) = 0