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
 

 

Updating the second table with the correct number

 
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: Wed Sep 19, 2007 1:21 am    Post subject: Updating the second table with the correct number
Reply with quote

I have a table with many rows for each policy number and the total number of rows are stored in another table. Unfortunately, for some policy numbers the values are not same. Now, I need to find all the policy numbers which are having the unmatched values and update the second table with the correct number (total of all the rows in the first table for each policy). I can do this by an application program but I want an SQL query for this. Can someone help?
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 Sep 19, 2007 2:33 am    Post subject:
Reply with quote

Hello,

If i needed to fix some incorrect counts, i would write the program and create an audit trail of the "bad" values and what they were corrected to be.

If you just run thru the tables changing numbers, you may cause yourself later problems if anyone has questions. Of course, i'd suggest a backup of the table to be updated before you begin.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Sep 19, 2007 8:11 am    Post subject: Reply to: Updating a Table
Reply with quote

Code:
TABLE    POLICY_DETAIL_TAB
-------------------------------------------------
POLICY_NO   POLICY_COLUMN1   POLICY_COLUMN2
-------------------------------------------------
1111      A1      A2
1111      B1      B2
1111      C1      C2
2222      D1      D2
2222      E1      E2
3333      F1      F2
-------------------------------------------------

Code:
TABLE   POLICY_COUNT_TAB
-------------------------------------------------
POLICY_NO   POLICY_COUNT
-------------------------------------------------
1111      3
2222      3
3333      1
-------------------------------------------------


Okay now let's say you have above mentioned 2 tables and you want to have an SQL that finds out the row about POLICY_NO 2222 since it does not have a proper count on POLICY_COUNT_TAB.

Query below should return you the policy numbers, that needs updated and are currently not correct. Now you may find some flaws in the query, since I didnt run it before posting. But in my hope, it should be a good starting point for you to dig in more.

Code:
SELECT    B.POLICY_NO
  FROM   POLICY_COUNT_TAB  B,
   POLICY_DETAIL_TAB A
 WHERE   B.POLICY_NO = A.POLICY_NO
   AND   B.POLICY_COUNT <> ( SELECT COUNT(*)
            FROM POLICY_DETAIL_TAB C
            GROUP BY C.POLICY_NO
            HAVING B.POLICY_NO = c.POLICY_NO);


Similarly something can be written to update too, but that I dont want to post w/o testing. Anyway, do not forget what Mr. Dick has advised on to take a backup before you run any update, so you always have a smily face at the end. icon_wink.gif
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Wed Sep 19, 2007 12:00 pm    Post subject: Re: Reply to: Updating a Table
Reply with quote

Hi

I tried above query. It gave -815 sqlcode.

Code:
SELECT    B.POLICY_NO
  FROM   POLICY_COUNT_TAB  B,
   POLICY_DETAIL_TAB A
 WHERE   B.POLICY_NO = A.POLICY_NO
   AND   B.POLICY_COUNT <> ( SELECT COUNT(*)
            FROM POLICY_DETAIL_TAB C
            GROUP BY C.POLICY_NO
            HAVING B.POLICY_NO = c.POLICY_NO);

'SQLCODE = -815, ERROR: A GROUP BY OR HAVING CLAUSE IS IMPLICITLY OR EXPLICITLY SPECIFIED IN A SUBSELECT OF A BASIC PREDICATE OR A SET CLAUSE OF AN UPDATE STATEMENT "

THen I remove GROUP BY / Having condition and Instead of those i used Where Clause

Code:
SELECT    B.POLICY_NO
  FROM   POLICY_COUNT_TAB  B,
   POLICY_DETAIL_TAB A
 WHERE   B.POLICY_NO = A.POLICY_NO
   AND   B.POLICY_COUNT <> ( SELECT COUNT(*)
            FROM POLICY_DETAIL_TAB C
            WHERE B.POLICY_NO = c.POLICY_NO);

The result was
POLICY_NO
2222
2222
NUMBER OF ROWS DISPLAYED IS 2

Can Anybody help to get Unique Rows instead of Duplicates .
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Sep 19, 2007 6:07 pm    Post subject: Reply to: Updating a Table
Reply with quote

Use disctinct.

Code:
SELECT    DISTINCT(B.POLICY_NO)
  FROM   POLICY_COUNT_TAB  B,
   POLICY_DETAIL_TAB A
 WHERE   B.POLICY_NO = A.POLICY_NO
   AND   B.POLICY_COUNT <> ( SELECT COUNT(*)
            FROM POLICY_DETAIL_TAB C
            WHERE B.POLICY_NO = c.POLICY_NO);
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Wed Sep 19, 2007 6:14 pm    Post subject:
Reply with quote

The SQL for collecting the data was given to OP a few days ago in another thread. I don't have the SQL for doing the updates. Why maintain a count when it can be quickly determined, it is just something else that can get out sync.
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Sep 20, 2007 11:14 am    Post subject: Re: Reply to: Updating a Table
Reply with quote

priyesh.agrawal wrote:
Use disctinct.

Code:
SELECT    DISTINCT(B.POLICY_NO)
  FROM   POLICY_COUNT_TAB  B,
   POLICY_DETAIL_TAB A
 WHERE   B.POLICY_NO = A.POLICY_NO
   AND   B.POLICY_COUNT <> ( SELECT COUNT(*)
            FROM POLICY_DETAIL_TAB C
            WHERE B.POLICY_NO = c.POLICY_NO);


Thanks priyesh

The Above query worked giving Unique Row values!!!
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 SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm
No new posts how to mask the phone number kumarinfy DB2 4 Mon Apr 03, 2017 5:23 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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