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

Need to generate a report as specified below


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sreenivasreddyg

New User


Joined: 23 Apr 2005
Posts: 39
Location: delhi

PostPosted: Tue Aug 21, 2007 5:59 pm
Reply with quote

I need to generate a report as specified below.

I have a table with multiple records for each policy number. The toal number of records should not match with the value in a column in another table. I tried using the join query as below. But it didn't work. Can someone help me in this.

select number from table1 a, table2 b
group by a.number
where count(*) != value;
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Aug 21, 2007 6:13 pm
Reply with quote

sreenivasreddyg wrote:
I need to generate a report as specified below.

I have a table with multiple records for each policy number. The toal number of records should not match with the value in a column in another table. I tried using the join query as below. But it didn't work. Can someone help me in this.

select number from table1 a, table2 b
group by a.number
where count(*) != value;


What results did you get. The more you share with us the better we can help you. Is there a common key between the two tables?


If number is the common key between the two tables then try this, I haven't tested it but you can try and reply with results.
Code:

SELECT A.NUMBER FROM (SELECT NUMBER, COUNT(*) AS RECORDS FROM TABLE1 GROUP BY NUMBER) A, TABLE2 B
WHERE A.NUMBER = B.NUMBER
   AND A.RECORDS <> B.VALUE
Back to top
View user's profile Send private message
sreenivasreddyg

New User


Joined: 23 Apr 2005
Posts: 39
Location: delhi

PostPosted: Tue Aug 21, 2007 7:01 pm
Reply with quote

Hi Craq,

Yes number is the common key. I have executed your query and is working fine. Thank you so much.
Back to top
View user's profile Send private message
sreenivasreddyg

New User


Joined: 23 Apr 2005
Posts: 39
Location: delhi

PostPosted: Tue Sep 18, 2007 10:36 pm
Reply with quote

Can I include update command to set the VALUE equal to RECORDS? My rqeuirement is to update the VALUE with teh RECORDS for each policy
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 Generate random number from range of ... COBOL Programming 3
No new posts Need help on formatting a report DFSORT/ICETOOL 14
No new posts Creating Report using SORT DFSORT/ICETOOL 7
No new posts Ca7 long running jobs report All Other Mainframe Topics 1
No new posts Report of batch jobs JCL & VSAM 1
Search our Forums:

Back to Top