I am trying to work on a query that looks like this. I am not that great with programming, still a learner. Not Sure what 0 - (Select count ...) means, and what is the purpose of this? Please ignore the table names... I just need to understand a SQL that says select count(whatever)......) = 0 or something mean, what it means...
Thanks for any help!
SELECT t.X_BENCH AS BENCH,
t.batch AS BATCH,
COUNT(DISTINCT t.TEST_NUMBER) AS EXCEPTION,
'Null_Reps' AS EXCEPTION_TYPE
FROM TEST t
INNER JOIN LABORATORY_ENTRY le
ON le.X_BENCH = t.X_BENCH
AND t.ANALYSIS = le.ANALYSIS
AND ( 0 =
** (SELECT COUNT(t1.TEST_NUMBER)**
** FROM TEST t1**
** WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER**
** AND t1.ANALYSIS = le.ANALYSIS**
** AND t1.STATUS IN ('I','P','C','A','X')**
** )** )
WHERE t.BATCH IS NOT NULL
AND t.CHANGED_ON > TRUNC(cvd_sysdate) - 30
AND (0 =
(SELECT COUNT(t1.TEST_NUMBER)
FROM TEST t1
WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER
It's just an inefficient way to verify that a row doesn't exist. NOT EXISTS would be better:
SELECT t.X_BENCH AS BENCH,
t.batch AS BATCH,
COUNT(DISTINCT t.TEST_NUMBER) AS EXCEPTION,
'Null_Reps' AS EXCEPTION_TYPE
FROM TEST t
INNER JOIN LABORATORY_ENTRY le
ON le.X_BENCH = t.X_BENCH
AND t.ANALYSIS = le.ANALYSIS
AND NOT EXISTS(
(SELECT 1
FROM TEST t1
WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER
AND t1.ANALYSIS = le.ANALYSIS
AND t1.STATUS IN ('I','P','C','A','X')
) )
WHERE t.BATCH IS NOT NULL
AND t.CHANGED_ON > TRUNC(cvd_sysdate) - 30
AND NOT EXISTS((SELECT COUNT(t1.TEST_NUMBER) FROM TEST t1 WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER))
I'm sure Scott was in a rush and meant to suggest something like:
AND NOT EXISTS(SELECT 1 FROM TEST t1 WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER)
If you include COUNT(*) in a sub-select SQL will actually go to the trouble of counting up all the rows ... and if you then don't USE that COUNT(*) value somewhere it was a waste of time.
EXISTS or NOT EXISTS just needs to establish if there is a single row that matches (and not worry if there is more than one row). If there is a suitable index available SQL will just pull the first matching entry from that (or fail to find one) and that takes a very short amount of time.
This is a Microsoft SQL Server forum - pretty sure that TRUNC() is for a different flavour of SQL, if that's the case then you might not find folk here that are familiar with your SQL platform.
I wonder if that first NOT EXISTS might be better as an OUTER JOIN (with a test in the WHERE clause to only include NULL values). Not sure if the NOT EXISTS would be correlated as efficiently as an OUTER JOIN ... but its not my strong suit! and no doubt Scott will put me straight on that
I do need the counts though. There is a outer query that gets the counts for all the "EXCEPTIONS". Are you suggesting that the 0 = ( SELECT COUNT(t1.TEST_NUMBER)**
** FROM TEST t1**
** WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER**
** AND t1.ANALYSIS = le.ANALYSIS**
** AND t1.STATUS IN ('I','P','C','A','X')**
** )**
logic only verifies that this record exists? I have come across some other logic that has 1<= comparison for the select count statement. Again, I am not understanding what is the need for this if we just need counts for certain records. Thanks for the explanation!
( 1 <=
(SELECT COUNT(t1.test_NUMBER)
FROM TEST t1
JOIN RESULT r1
ON r1.TEST_NUMBER = t1.TEST_NUMBER
WHERE t1.SAMPLE_NUMBER = t.SAMPLE_NUMBER
AND t1.ANALYSIS = le.ANALYSIS
AND t1.status IN ('C', 'R')
AND ((r1.DOUBLE_ENTRY_CHK = 'F')
OR (r1.IN_SPEC = 'F'
AND r1.PANIC_INDICATOR IN ('L', 'H'))
OR (r1.IN_SPEC = 'T'
AND r1.IN_CONTROL = 'F'
AND r1.CONTROL_1 IN ('L', 'H')))
) )
They need to be in the SELECT then, not the WHERE / JOIN conditions
Yup - but it does so by taking the time to count every matching row, and then checking if that = 0
You do, also, have a COUNT() in the SELECT statement - so its only the WHERE / JOIN ones that are wasting CPU cycles and I/O
Dunno about the SQL flavour you are using, but in MS SQL you cannot have an aggregate function, such as COUNT(DISTINCT t.TEST_NUMBER), in the SELECT unless you also use GROUP BY
This is a slightly different query and my question is around using the operators with count and the same count sub qry being used multiple times. That is where I start to loose it.
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;
get rid of that, as I explained in your previous thread, and use EXISTS instead. If the COUNT finds a large number of matching rows it will be a massive performance killer.
Looks like you are using Oracle. This is a Microsoft SQL Server forum, so there may not be people here skilled enough (in oracle) to give you good advice.
I do need a sum in the end. That is part of my outer query (the very first select). I cannot get rid of counts in this case. In the later thread, count was not being used so it made sense there, at least from what I am understanding.
Do you have any feedback on my second question? I just copied this part from my first post. The count sub qry is being used twice with the same logic. From SQL perspective, what difference does that make? I am not worried about any feedback on TRUNC or TO_CHAR.
Thanks for the help!
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,
Because Oracle may not support the syntax that I would use to solve this - CTEs and OUTER APPLY. Maybe it does. maybe its a bit different. In the past I've spent time writing some code to help someone and then found out it was a waste of my time because the non-MS version of SQL they were using didn't support that syntax.
