Hi All,
I need some help understanding this SQL. There are multiple 'unions' used with some sub querys to return the count. My biggest lag here is the use of counts in the subqueries with something like 1<= (select count etc..) I haven't dealt with SQL written like this and not able to make much sense out of it. I will appreciate any help!!
Many Many Thanks!!
SELECT BATCH,
''
||REPLACE(TO_CHAR(SUM(EXCEPTION)), '.0')
||''
FROM
(SELECT t.X_BENCH,
t.batch,
COUNT(DISTINCT t.TEST_NUMBER) AS EXCEPTION,
'Flag' AS EXCEPTION_TYPE
FROM TEST t
INNER JOIN LABORATORY_ENTRY le
ON le.X_BENCH = t.X_BENCH
AND t.ANALYSIS = le.ANALYSIS
AND ( 1 <= (
(SELECT COUNT(t1.TEST_NUMBER)
FROM TEST t1
JOIN FLAGS f
ON f.OBJECT_ID = t1.TEST_NUMBER
WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER
AND t1.ANALYSIS = le.ANALYSIS
AND t1.STATUS IN ('C', 'R')
AND f.X_FLAG_TYPE IN ('QC_VIOL', 'RULE_VIOL')
AND t1.HAS_FLAGS = 'T'
) ))
WHERE t.BATCH IS NOT NULL
AND (1 <= (
(SELECT COUNT(t1.TEST_NUMBER)
FROM TEST t1
INNER JOIN FLAGS f
ON f.OBJECT_ID = t1.TEST_NUMBER
WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER
AND t1.ANALYSIS = t.ANALYSIS
AND t1.STATUS IN ('C', 'R')
AND f.X_FLAG_TYPE IN ('something', '1')
AND t1.HAS_FLAGS = 'T'
) ))
GROUP BY t.X_BENCH,
t.BATCH
UNION ALL
similar sub query with a different criteria
GROUP BY t.x_bench,
t.batch
)
WHERE BATCH IN ('ABC')
GROUP BY batch;