Changeset 30062 for trunk/ippTools/share/regtool_pendingexp.sql

Timestamp:

Dec 15, 2010, 11:39:43 PM (15 years ago)

Author:

watersc1

Message:

hopefully this one will fix the problem

File:

• trunk/ippTools/share/regtool_pendingexp.sql (modified) (1 diff)

trunk/ippTools/share/regtool_pendingexp.sql

@@ -1 +1 @@
 SELECT DISTINCT
-    exp_id,
-    tmp_exp_name,
-    tmp_camera,
-    tmp_telescope,
-    state,
-    workdir,
-    workdir_state,
-    reduction,
-    dvodb,
-    tess_id,
-    end_stage,
-    label,
-    camera,
-    filter,
-    obs_mode,
-    obs_group,
-    epoch,
-    dateobs
-FROM
-    (SELECT
-       newExp.*,
-       newImfile.tmp_class_id,
-       rawImfile.tmp_class_id as raw_tmp_class_id,
-       rawImfile.camera,
-       rawImfile.filter,
-       rawImfile.dateobs,
-       rawImfile.obs_mode,
-       rawImfile.obs_group
-    FROM newExp
-    JOIN newImfile
-       USING(exp_id)
-    LEFT JOIN rawExp
-       USING(exp_id)
-    LEFT JOIN rawImfile
-        ON newImfile.exp_id = rawImfile.exp_id
-        AND newImfile.tmp_class_id = rawImfile.tmp_class_id
-    WHERE
-        newExp.state = 'run'
-        AND rawExp.exp_id IS NULL
-        AND rawImfile.data_state = 'full'
--- where hook %s
-    GROUP BY
-        newExp.exp_id
-    HAVING
-        COUNT(newImfile.tmp_class_id) = COUNT(rawImfile.tmp_class_id)
-        AND SUM(rawImfile.fault) = 0
+     exp_id,
+     tmp_exp_name,
+     tmp_camera,
+     tmp_telescope,
+     state,
+     workdir,
+     workdir_state,
+     reduction,
+     dvodb,
+     tess_id,
+     end_stage,
+     label,
+     camera,
+     filter,
+     obs_mode,
+     obs_group,
+     epoch,
+     dateobs
+FROM 
+        (
+        select DISTINCT * from 
+         (
+          select newExp.*,count(newImfile.tmp_class_id) AS new_count
+          from newExp
+          LEFT JOIN newImfile USING (exp_id)
+          LEFT JOIN rawExp USING (exp_id) 
+          where 
+           newExp.state = 'run' AND rawExp.exp_id IS NULL
+          GROUP BY newExp.exp_id
+         ) AS NEWEXPOSURES
+         JOIN 
+         (
+          select DISTINCT newExp.exp_id,
+          rawImfile.camera,
+          rawImfile.filter,
+          rawImfile.dateobs,
+          rawImfile.obs_mode,
+          rawImfile.obs_group,
+          count(rawImfile.tmp_class_id) AS raw_count
+          from newExp
+          LEFT JOIN rawExp USING (exp_id) 
+          LEFT JOIN rawImfile USING (exp_id)
+          where 
+           newExp.state = 'run' AND rawExp.exp_id IS NULL
+           AND rawImfile.data_state = 'full'
+           -- where hook %s
+          GROUP BY newExp.exp_id
+          HAVING SUM(rawImfile.fault) = 0
+         ) AS RAWEXPOSURES
+         USING (exp_id)
+        WHERE raw_count = new_count 
+    ) AS Foo
 -- limit hook %s
-    ) as Foo