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
 

 

Get counts of all columns having value > space

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

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 12:13 pm    Post subject: Get counts of all columns having value > space
Reply with quote

hi,

i need to get counts of all columns in a table having value > space.
can i get it using single query?if yes,please let me know the query syntax.

thanks
rups
Back to top
View user's profile Send private message

guptae

Moderator


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

PostPosted: Fri Jan 16, 2009 1:19 pm    Post subject:
Reply with quote

Hellp Rups,

Your query is not very clear.
Do you want to find number of rows having value greater than space for particular column?
Back to top
View user's profile Send private message
rups

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 2:13 pm    Post subject: Reply to: Get counts of all columns having value > space
Reply with quote

hello guptae,

yes i want to find total no of rows having value greater than space of all the column present in a table.


suppose if i have a table just like below

emp-id name
1 xx
2
3 yy


Query should give me answer following answer

emp-id = 3(since all the three rows has data)
name=2(only two rows has data)

i want to get the above result by executing single query.






thanks
rups
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jan 16, 2009 2:25 pm    Post subject:
Reply with quote

Try

Code:


SELECT COUNT(EMPID),COUNT(NAME) FROM TABLE WHERE EMPID > '' OR NAME > '' ;



not tested ...
Back to top
View user's profile Send private message
rups

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 2:48 pm    Post subject: Reply to: Get counts of all columns having value > space
Reply with quote

hi ashimer,

i tried your query,but i got the answer of emp-id count as 3 and name as 3.

thanks
rups
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Jan 16, 2009 3:26 pm    Post subject:
Reply with quote

Try
Code:

SELECT COUNT(empid)
      ,(SELECT COUNT(name)
          FROM table1
         WHERE name > ' '
        )
  FROM table1
 WHERE empid > ' '
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Jan 16, 2009 3:33 pm    Post subject:
Reply with quote

Hello,
Quote:
tried your query,but i got the answer of emp-id count as 3 and name as 3.
And what did you expect ? Your table definition is not known to us so no one (me) can not comment much on your this post.
Back to top
View user's profile Send private message
rups

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 3:56 pm    Post subject: Reply to: Get counts of all columns having value > space
Reply with quote

hello anuj,

here is the structure of the table

Emp-id Name
1 xx
2
3 yy


emp-id is defined as char(3) NOT NULL
name as char(10) NOT NULL
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Fri Jan 16, 2009 5:12 pm    Post subject:
Reply with quote

Code:
SELECT 'EMPID', COUNT(EMP-ID)                       
FROM EMPTAB WHERE EMP-ID<>SPACE(3)   
UNION                                           
SELECT 'EMPNAME',COUNT(NAME)                       
FROM EMPTAB WHERE NAME<>SPACE(10);
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 IMS T-Pipe queue counts in a COBOL Pr... Siva NKK Kothamasu IMS DB/DC 2 Tue May 09, 2017 6:31 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts VSAM Space Allocation mrgnndhmk JCL & VSAM 7 Sat Apr 22, 2017 12:42 am
This topic is locked: you cannot edit posts or make replies. Need help in estimating space of unlo... ashek15 IMS DB/DC 12 Fri Apr 07, 2017 5:11 am
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am


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