Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
No new posts Sum Fields ballaswaroop DFSORT/ICETOOL 4 Tue May 02, 2017 11:07 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us