Multiple Fields = Single Key

socker_dad · Posted: Thu Apr 01, 2010 4:55 am

I am working with a Count Distinct query where the distinct value needs to be "calculated" on two fields: a date field and an alphanumeric field.

The combination of these two fields for a unique key.

I am at a loss as to how to put this in a DB2 query. I know how to do this on a single field, but not on multiple fields. My fields are CH07_06 and DT01.

I've thought of using another distinct in a subquery, but that results in erroneous counts as well. Any suggestions?

CICS Guy · Posted: Thu Apr 01, 2010 5:19 am

The argument of COUNT(expression) or COUNT(ALL expression) is a set of values. The function is applied to the set of values derived from the argument values by the elimination of null values. The result is the number of nonnull values in the set, including duplicates.
I understand your problem, the manual doesn't seem to be much help....
Expressions
And how do you define the "expression" in the COUNT function?

guptae · Posted: Thu Apr 01, 2010 1:06 pm

Hi there,
I tried the below sql & its worked for me . I agree that this i snot efficent one bt will give you desired result

socker_dad · Posted: Fri Apr 02, 2010 1:05 am

Thanks Ekta - that did the trick. (Funny how all my manuals never mentioned using two values in a Distinct phrase!)

CICS Guy · Posted: Fri Apr 02, 2010 2:12 am