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
 

 

Need to generate a report as specified below

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need to generate a report as specified below
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    Post subject: Re: DB2 query
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    Post subject:
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    Post subject:
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    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 Splitting of single report to seperat... abdulrafi TSO/ISPF 7 Mon Mar 27, 2017 3:59 pm
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Easytrieve report shalem CA Products 4 Sun Feb 26, 2017 9:08 am
No new posts Detailed Transaction History report f... Kyle Carroll CICS 5 Tue Oct 18, 2016 12:42 am
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am


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