SQLTeam.com | Weblogs | Forums

SQL Counts with >=1 etc.. Help interpret


#1

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;


#2

its basically saying the count of the subquery is >= 1
different way of writing it. might be my left handed brother :slight_smile:


#3

Answered in your previous post:

Please reply, in that thread, with a question as to what you didn't understand in the answer