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
 

 

Count using multiple ranges

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

New User


Joined: 24 Jun 2009
Posts: 9
Location: India

PostPosted: Wed Oct 28, 2009 10:20 pm    Post subject: Count using multiple ranges
Reply with quote

I need to write a query to get count for multiple criterias within same column. Eg -

Column A
900001
900002
900300
905000
990001
990005
..
..
999999

I need to collect information in following format -
Col A having first 3 digits as 900 Count is 10
Col A having first 3 digits as 901 Count is 100
Col A having first 3 digits as 902 Count is 15
...
...
Col A having first 3 digits as 999 Count is 15

Can I do this using single query. I need to run the using DB2 Sql.

Thanks and Regards, Rohit
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 28, 2009 10:29 pm    Post subject:
Reply with quote

look at the case statement; will do what you want to do.

there are also examples in the forum - there are examples from today's posts.

you did not bother to tell us the data type of column a,
so no one can give you a 'real' solution.
Back to top
View user's profile Send private message
rohitnargunde

New User


Joined: 24 Jun 2009
Posts: 9
Location: India

PostPosted: Wed Oct 28, 2009 11:01 pm    Post subject: Reply to: Count using multiple ranges
Reply with quote

Thanks for the reply. I tried using Case but I wasn't successful. Data type of Column in Alphanumeric.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 28, 2009 11:47 pm    Post subject:
Reply with quote

Hello,

If you actually want help, you need to post what syntax you tried and the error from that try.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Oct 29, 2009 12:16 am    Post subject:
Reply with quote

Quote:
Data type of Column in Alphanumeric.


part of your problem is that you do not know DB2 data types.

no such thing as a db2 data type of alphanumeric.

it is either char or one of the numeric types.

if char, use substr
if numeric, cast to char and then substr.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Oct 29, 2009 1:08 pm    Post subject:
Reply with quote

what's wrong with : group by substr(colA,1,3) ?
Back to top
View user's profile Send private message
rohitnargunde

New User


Joined: 24 Jun 2009
Posts: 9
Location: India

PostPosted: Sun Nov 01, 2009 12:17 am    Post subject: Reply to: Count using multiple ranges
Reply with quote

i am sorry.. but i do know DB2 data types... i had mentioned alphanumeric because that's what is been used in the DCLGEN copybook..

I had joined the forum to enhance my knowledge and get out of woods when in need. I feel bad that experts on the topic make insensitive statements.

getting back to the topic. Can anyone send me some samples.. if they have handy.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Nov 01, 2009 1:29 am    Post subject:
Reply with quote

There is a beginners website associated with this forum,
exactly for people who have no interest in researching or reading the manuals.

and believe me, no-one in this thread was insensitive - except you.
no-one is paid for their contributions,
unlike you who is paid to accomplish a task
for which you seem to think that members of this board should provide solutions.

link for v7 sql reference

vsn 8 Application programmers guide

Redbook: DB2 for z/os Application Programming

There are samples in the manuals.

if cobol is your programming language alphanumeric (x-type) is the equivalent of Character (char)

If it is indeed Char, a combination of CASE substr would work.
but actually, GuyC made an excellent suggestion:
GROUP BY SUBSTR(col,1,3)

why did you not try that?
There would have been no need to invest time in learning CASE,
but unfortunately, you need to understand SUBSTR -uhmm, actually not;
GuyC gave you the answer.

If you wait long enough, probably someone will provide a complete solution,
free of charge,
which you can then submit as your own work,
amaze your co-workers
(with whom you apparently do not communicate)
- and collect your paycheck.
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 Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 4 Fri Mar 24, 2017 2:24 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm


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