[quoted text, click to view] gilgantic wrote:
>
> I am trying to fine tune or rewrite my SELECT statement which has a
> combination of SUM and CASE statements. The values are accurate, but
> the query is slow.
> BUSINESS RULE
> =============
> 1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both
> have a value.
> 2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.
> 4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse the
> SQL from both Count1 and Count2)
> 3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.
I can see a few problems here.
[quoted text, click to view] > SQL Code
> ========
> SELECT
> SUM(CASE
> WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL)
> OR (FIELD_1 IS NOT NULL AND FIELD_2 IS NOT NULL))
> THEN 1
> ELSE 0
> END) AS Count1 ,
Since this case looks at FIELD_1 being not null regardless of
the state of FIELD_2, why not just test FIELD_1?
[quoted text, click to view] > SUM(CASE
> WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
> THEN 1
> ELSE 0
> END) AS Count2,
> SUM(CASE
> WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
> THEN 1
> ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL)
> OR FIELD_1 IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)
> END) AS Total_Count,
Since this is the sum of Count1 and Count2, why not
just present Total_Count as Count1 + Count2?
[quoted text, click to view] > SUM(CASE
> WHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)
> THEN 1
> ELSE 0
> END) AS None_Count,
> FROM
> TABLE_1
In all cases, the "ELSE 0" is redundant. However, since
you posted to newsgroups for 3 separate products, its not
clear which one you're using. I'd be more inclinded to
rewrite this using charactistic functions.