Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Multiple Fields = Single Key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Thu Apr 01, 2010 4:55 am    Post subject: Multiple Fields = Single Key
Reply with quote

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?

Code:

SELECT COUNT(DISTINCT(CH07_06))
FROM TDLVAR
     etc.
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Thu Apr 01, 2010 5:19 am    Post subject:
Reply with quote

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? icon_confused.gif
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Apr 01, 2010 1:06 pm    Post subject:
Reply with quote

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

Code:
SELECT COUNT(*) FROM (SELECT DISTINCT CH07_06, DT01 FROM TDLVAR) AS NEW_TAB;
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Apr 02, 2010 1:05 am    Post subject:
Reply with quote

Thanks Ekta - that did the trick. (Funny how all my manuals never mentioned using two values in a Distinct phrase!)
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri Apr 02, 2010 2:12 am    Post subject:
Reply with quote

DB2 V9.1 for z/OS SQL Reference wrote:
Code:
    ________________________________________________________________________
   |                                                                        |
   |              _ALL______                                                |
   | >>__SELECT__|__________|_____________________________________________> |
   |             |_DISTINCT_|                                               |
   |                                                                        |
   | >__ _*_____________________________________________ ________________>< |
   |    | <_,_________________________________________  |                   |
   |    |__ _expression_ _________________________ __ |_|                   |
   |       |            |  _AS_                   |  |                      |
   |       |            |_|____|__new-column-name_|  |                      |
   |       |__ _table-name_______ _.*________________|                      |
   |          |_view-name________|                                          |
   |          |_correlation-name_|                                          |
   |                                                                        |
   |________________________________________________________________________|
Where
expression
Can be any expression of the type that is described in "Expressions" in topic 2.22. Each column-name in the expression must unambiguously identify a column of R.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sum the fields in particular format f... sharana64 DFSORT/ICETOOL 9 Fri Jan 05, 2018 1:11 am
No new posts SORT BUT RETAIN HIGHEST VALUE ON NON-... leondan22 DFSORT/ICETOOL 2 Thu Dec 14, 2017 8:13 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Formatting multiple records to a sing... Vignesh Sid SYNCSORT 3 Tue Nov 07, 2017 12:22 pm
No new posts Updating multiple counts in trailer!! Vignesh Sid SYNCSORT 4 Mon Nov 06, 2017 1:04 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us