IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Count using multiple ranges


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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

Moderator Emeritus


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

PostPosted: Wed Oct 28, 2009 11:47 pm
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
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: 1281
Location: Belgium

PostPosted: Thu Oct 29, 2009 1:08 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top